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

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

tom_abramov — Tue, 12 Mar 2019 07:22:12 GMT

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.

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

gzmorgan0 — Tue, 12 Mar 2019 10:06:06 GMT

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

Select Data Type > Numeric,
Modeling Type > Continuous,
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.

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

tom_abramov — Tue, 12 Mar 2019 15:38:03 GMT

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

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

gzmorgan0 — Tue, 12 Mar 2019 20:23:03 GMT

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.

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

tom_abramov — Wed, 13 Mar 2019 07:06:33 GMT

Thank you,

I am using JMP 14 Pro.

Still getting missing values - see attached screenshot.

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

gzmorgan0 — Wed, 13 Mar 2019 09:44:02 GMT

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.

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

ms — Wed, 13 Mar 2019 09:53:54 GMT

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

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

tom_abramov — Wed, 13 Mar 2019 13:19:35 GMT

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

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

gzmorgan0 — Wed, 13 Mar 2019 20:18:06 GMT

@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

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

Kiwi — Fri, 15 Jul 2022 15:20:04 GMT

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