Choose Language Hide Translation Bar
Highlighted
Level II

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## 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

);

Jim
6 REPLIES 6
Highlighted
Staff

## 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.

JMP Systems Engineer, Pharm and BioPharm Sciences
Highlighted
Level II

## 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!

Highlighted
Super User

## 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

);

Jim
Highlighted
Level II

## 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!

Highlighted
Super User

## 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)

);

Level I

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

How about converting from Military Time to regular?

Article Labels

There are no labels assigned to this post.