cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
tom_abramov
Level V

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

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

 

 

View solution in original post

9 REPLIES 9
gzmorgan0
Super User (Alumni)

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. 

tom_abramov
Level V

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

gzmorgan0
Super User (Alumni)

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 

tom_abramov
Level V

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.

gzmorgan0
Super User (Alumni)

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.

ms
Super User (Alumni) ms
Super User (Alumni)

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

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...
gzmorgan0
Super User (Alumni)

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 

Kiwi
Level I

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

That formula fails for certain conditions:

 

Time_1:   6/16/2022 11:20:49 AM
Time_2:   6/16/2022 12:45:13 PM

 

If we consider Time_1 we will gain --> 6/16/2022 11:20:49

However, if we consider Time_2 we will gain --> 7/16/2022 00:45:13 (switching to the wrong day).

 

Is there an easy way to convert a timestamp like Time_1 or Time_2 into a continous numeric instead of a string?

 

regards Sven