Subscribe Bookmark RSS Feed

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

nadershakerin0

New Contributor

Joined:

May 12, 2016

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

);

Jim
6 REPLIES
Byron_JMP

Staff

Joined:

Apr 26, 2012

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 )

Insets 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

StartStep 1Step 2Step 3
3000300:0312:03 AM
30003000:3012:30 AM
1130113011:3011:30 AM
1830183018:306: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.

nadershakerin0

New Contributor

Joined:

May 12, 2016

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! 

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  

);

Jim
nadershakerin0

New Contributor

Joined:

May 12, 2016

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

ms

Super User

Joined:

Jun 23, 2011

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)

);


khoepfl

Community Member

Joined:

Sep 8, 2016

How about converting from Military Time to regular?