topic Re: Converting Military Time Format (hhhh) to Time Format (hh:mm) in Discussions

Converting Military Time Format (hhmm) to Time Format (hh:mm)

nadershakerin0 — Fri, 13 May 2016 06:32:34 GMT

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)

Byron_JMP — Tue, 30 Oct 2018 19:13:40 GMT

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.

Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)

txnelson — Sat, 14 May 2016 03:31:22 GMT

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 (hhmm) to Time Format (hh:mm)

nadershakerin0 — Sun, 15 May 2016 00:46:29 GMT

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 (hhmm) to Time Format (hh:mm)

nadershakerin0 — Sun, 15 May 2016 00:47:31 GMT

Jim, your solution worked perfectly. I like how everything remained in the numeric form. Thank you very much!

Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)

ms — Wed, 27 Jan 2021 19:06:03 GMT

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 (hhhh) to Time Format (hh:mm)

khoepfl — Thu, 05 Jan 2017 18:51:29 GMT

How about converting from Military Time to regular?

Re: Converting Military Time Format (hhhh) to Time Format (hh:mm)

Madwolf — Wed, 27 Jan 2021 16:31:05 GMT

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)

Madwolf — Wed, 27 Jan 2021 19:05:21 GMT

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)

Madwolf — Fri, 29 Jan 2021 15:23:17 GMT

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)

txnelson — Fri, 29 Jan 2021 15:51:26 GMT

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)

Madwolf — Fri, 29 Jan 2021 17:00:10 GMT

It works! Thank you so much. I learnt so much just by looking at the formulas you proposed!