cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
nadershakerin0
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
txnelson
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

View solution in original post

11 REPLIES 11
Byron_JMP
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, Health and Life Sciences (Pharma)
nadershakerin0
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! 

txnelson
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
nadershakerin0
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!   

Madwolf
Level II

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.

Madwolf
Level II

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!

Madwolf
Level II

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?
txnelson
Super User

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;
Jim
Madwolf
Level II

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!