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

[#1]

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.

[#2]

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. 

[#3]

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

[#4]

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.

 

[#5]

Thank you,

I am using JMP 14 Pro.

Still getting missing values - see attached screenshot.

[#6]

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.

[#7 · Accepted solution]

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]

Thank you, it is ok with formula.
Probably there is no way without formula...

[#9]

@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 

[#10]

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