BookmarkSubscribe
Choose Language Hide Translation Bar
tom_abramov
Community Trekker

Convert AM/PM time text to 24h time format - no JSL

Hi,

I'm having trouble converting values like 

08:49:46 PM
10:30:46 AM

from Character Column to Numeric h:m:s 24h format.

I would like to run the conversion without JSL and without depending on Operation system time format. 

 

Please assist.

Regards,

Tom.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

It's probably the "PM" that causes it to fail. Informat() should work if using just the time part of the string, e.g. by using the Left() function and conditionally correct for the AM/PM.

 

Try this formula:

Informat(Left(:Column 1, 8), "h:m:s") + If(Contains(:Column 1, "AM"), 0, In Hours(12))

 

 

8 REPLIES 8
Highlighted
gzmorgan0
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

If your column is character, then you can do this with point an click. Right click on the column and do all these steps befor pressing OK 

  1. Select Data Type > Numeric,
  2. Modeling Type > Continuous,
  3. Format > Duration > hr:m:s

If you wnat to not risk the current column, create a new column that is numeric and continuous, and use the formula Informat( char_column, "hr:m:s"). Then change the format to Duration. 

0 Kudos
tom_abramov
Community Trekker

Re: Convert AM/PM time text to 24h time format - no JSL

Thank you, but unfortunately, I get missing values after the changes you mentioned...

0 Kudos
gzmorgan0
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

Which version of JMP are you using? There was one version where conversion in place did not work.

 

I suggest you make a new column, and use the  function Informat( Trim(your_char_column), "h:m:s"). This should return numbers then change that column's format to Duration > hr:m:s. The Trim() function will remove spaces and other unprintable characters. I hope that works.

image.png 

0 Kudos
tom_abramov
Community Trekker

Re: Convert AM/PM time text to 24h time format - no JSL

Thank you,

I am using JMP 14 Pro.

Still getting missing values - see attached screenshot.

0 Kudos
gzmorgan0
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

What is the data type of Column 1? I can see the modeling type is nominal. If Column 1 is Numeric and  Nominal, Informat returns an empty().

 

If the column is already numeric, just change the Format  of Column 1 to Duration.

0 Kudos
ms
Super User ms
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

It's probably the "PM" that causes it to fail. Informat() should work if using just the time part of the string, e.g. by using the Left() function and conditionally correct for the AM/PM.

 

Try this formula:

Informat(Left(:Column 1, 8), "h:m:s") + If(Contains(:Column 1, "AM"), 0, In Hours(12))

 

 

tom_abramov
Community Trekker

Re: Convert AM/PM time text to 24h time format - no JSL

Thank you, it is ok with formula.
Probably there is no way without formula...
0 Kudos
gzmorgan0
Super User

Re: Convert AM/PM time text to 24h time format - no JSL

@tom_abramov

You have a solution from @ms response.  However, we either have very different JMP versions or we were not communicating very well, so this is an FYI response. 

 

Your screenshot showed nominal modeling but only displayed time, 08:49:46 PM.  If this column was numeric, not character then Informat() will return an empty because it's argument needs to be a string/character.  So, it is important to specify whether your data is numeric or character, and or attach a sample data table.

 

If your incoming column is a DateTime column just formatted as time and it is numeric, I suggest you create a new column using the function/formula Time of Day(column 1) then format that new column as Duration.  Even when a column is formatted as just time, if I wanted to find the time between say row 1 and row 2 and of the DateTime column just formatted as time it would include teh number of days, the underlying data is still datetime.

 

From the Main Menu, select Help > Scripting Index > Functions > Date Time there are example scripts you can run to see how each function works, whether its argument is string or numeric.  This is an easy way to learn more. FYI