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

[#1]

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.

[#2]

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.

[#3]

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! 

[#4 · Accepted solution]

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  

);

[#5]

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

[#6]

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.

[#7]

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!

[#8]

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?

[#9]

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;

[#10]

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