I need to convert military time in the hhmm format, e.g. 1432 or 725 to hh:mm format e.g. 14:32 to 7:25. Is there a simple formula that can make the conversion, or should I use a script for it? Thanks.
Here is a different way of doing the same thing that Bryon is showing. My version goes directly to a numeric time value, rather than creating a character value that is then input.
Names Default To Here( 1 );
// Create a sample data table with some Military times.....
// The column can be either numeric or character...the conversion
// formula handles both
dt = New Table( "Untitled 3", Add Rows( 2 ), New Column( "Military", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1421, 923] ) ) );
// The wait() functions are just there to add a dramatic affect
Wait( 2 );
// Add a new column to hold the converted times
// Use a time format since the conversion will create
// a value which is the number of seconds since midnight
dt << New Column( "time", Numeric, "Continuous", Format( "hr:m", 12 ) );
// Run across all rows and do the conversion
// The formula will work as a column formula by just removing
// the ":time="
For Each Row(
:time = Num( Substr( Char( :Military ), -2 ) ) * 60 +
Num( Substr( Char( :Military ), 1, (Length( Char( :Military ) ) - 2) ) ) * 3600
);
There is likely a simpler way to do this; however, this works pretty well.
Step 1
Repeat( "0", 4 - Length( Char( :Start ) ) ) || Char( :Start )
Makes the staring number into a 4 digit character padded with zeros (this is handy for zip codes too)
Step 2
Left( :Step 1, 2 ) || ":" || Right( :Step 1, 2 )
Inserts a colon between the hours and minutes
Step 3
Format( Informat( :Char 24 hr Time, "h:m" ), "h:m" )
This formula takes the character 24 hr time and turns it into the number of seconds after midnight, then it turns the seconds into 12 hour time.
Informat evaluates a character string and returns a number
format evaluates a numeric strings and returns a character
........
or you could do it all in one "simple" step with one formula
Format( Informat( Left( Repeat( "0", 4 - Length( Char( :Start ) ) ) || Char( :Start ), 2 ) || ":" || Right( Repeat( "0", 4 - Length( Char( :Start ) ) ) || Char( :Start ), 2 ), "h:m" ), "h:m" );
I keep a collection of fierce formulas that get recycled for special cases.
Byron, thank you very much for your guidance. Your proposed method works; however, it keeps the variable in character state.
I have saved your script for future special cases!
Jim, your solution worked perfectly. I like how everything remained in the numeric form. Thank you very much!
Still another approach that avoids strings altogether:
dt << New Column( "time", Numeric, "Continuous", Format( "hr:m", 12 ), Formula( Floor( :Military / 100 ) * 3600 + Mod( :Military, 100 ) * 60 ) );
How about converting from Military Time to regular?
Hi Jim,
How do I use this script? What are the variables that I needed to change?
Then name of the dataset is "transaction_data" and the name of the column that resides the military time is "DAY" (transaction_data:DAY)
So, how should I replace these 2 variables into you script and run?
Sorry for such a noob question.
I posted your code, into the formula section of the new field, changed the field to my dataset column name, and it works perfectly!
Num( Substr( Char( :Military ), -2 ) ) * 60 + Num( Substr( Char( :Military ), 1, (Length( Char( :Military ) ) - 2) ) ) * 3600
Thank you so much!
