Converting Military Time Format (hhmm) to Time Format (hh:mm)<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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.
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)It works! Thank you so much. I learnt so much just by looking at the formulas you proposed!Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)My first question is, if the length of the input time value is less than 4 characters long, should one assume there are zeros (0) that are missing from the left side of the military time? If this is the case, I propose the solution below; It pads the value with leading zeros and then performs the conversion.
standard = Substr( "00", Length( Char( :Military ) ) - 1 ) ||
Char( :Military );
Num( Substr( Char( :standard ), -2 ) ) * 60
+Num(
Substr(
Char( :standard ),
1,
(Length( Char( :standard ) ) - 2)
)
) * 3600;
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)I attempted to use this code to convert a string into a time format (hh:mm)
Num( Substr( Char( :Military ), -2 ) ) * 60 + Num(
Substr( Char( :Military ), 1, (Length( Char( :Military ) ) - 2) )
) * 3600
However, I realised if the string is only 2 digits, it won't get converted. For instance if it is 26, it won't be converted to 12:26am. However, a 3 digit string works fine (100 becomes 1:00am)
How is that so?Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)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!
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)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.
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)How about converting from Military Time to regular?
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)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 )
);
Re: Converting Military Time Format (hhmm) to Time Format (hh:mm)Jim, your solution worked perfectly. I like how everything remained in the numeric form. Thank you very much!
Re: Converting Military Time Format (hhmm) to Time Format (hh:mm)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!
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)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 ) );
Wait( 2 );
// 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
);
Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)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
| Start |
Step 1 |
Step 2 |
Step 3 |
| 3 |
0003 |
00:03 |
12:03 AM |
| 30 |
0030 |
00:30 |
12:30 AM |
| 1130 |
1130 |
11:30 |
11:30 AM |
| 1830 |
1830 |
18:30 |
6:30 PM |
........
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.
