topic Re: MM/DD HH:MM conversion how to do in Discussions

MM/DD HH:MM conversion how to do

TONG_SUPER — Mon, 26 Aug 2024 23:05:40 GMT

I have a time Colum in a odd format ( do not even have Year in and need help converting to a simply YY/MM/DD HH:MM

Thanks

tong

Time

[08/06-19:07:34.981]

[08/06-19:08:05.927]

[08/06-19:08:14.270]

[08/06-19:08:26.331]

Re: MM/DD HH:MM conversion how to do

txnelson — Mon, 26 Aug 2024 23:50:42 GMT

Here is the formula I used to do the conversion

Date MDY( Num( Word( 1, :Time, "[/" ) ), Num( Word( 2, :Time, "[/-" ) ), 2024 )
+Informat( Word( 3, :Time, "[/-]" ), "hh:mm:ss" )

Re: MM/DD HH:MM conversion how to do

TONG_SUPER — Tue, 27 Aug 2024 20:31:56 GMT

Thank you so much

Re: MM/DD HH:MM conversion how to do

TONG_SUPER — Wed, 28 Aug 2024 18:17:24 GMT

I had another question. I can do MM/DD/YYYY but i do not know how to add the time string in to add in it

Source Table

20240207_163752

20240208_132212

20240320_180945

20240321_062705

DMY conver=Date DMY(Num( Munger( :Source Table, 7, 2 ) ),Num( Munger( :Source Table, 5, 2 ) ),Num( Left( :Source Table, 4 ) ))

Re: MM/DD HH:MM conversion how to do

txnelson — Wed, 28 Aug 2024 18:24:32 GMT

JMP Date/Time values are stored as the number of seconds since Midnight, January 1, 1904. So once you have input the Date part using the DateDMY, the time part just needs to be added to the date part. The time part simply becomes the number of hours*3600 + number of minutes*60 + number of seconds. I don't know how to interpret the 163752, but all you need to do is to determine the number of seconds in the day it represents and then add it to the Date part, and you will have your answer.

Re: MM/DD HH:MM conversion how to do

TONG_SUPER — Wed, 28 Aug 2024 19:09:09 GMT

sorry i was not clear. 163752 is kind of military time for HHMMSS eg 4 pm 37 min and 52 sec

Re: MM/DD HH:MM conversion how to do

txnelson — Wed, 28 Aug 2024 20:00:23 GMT

Given the method I specified above, the formula is

DMY conver = Date DMY(
	Num( Munger( :Source Table, 7, 2 ) ),
	Num( Munger( :Source Table, 5, 2 ) ),
	Num( Left( :Source Table, 4 ) )
) + Num( Munger( :Source Table, 10, 2 ) ) * 3600
+Num( Munger( :Source Table, 12, 2 ) ) * 60
+Num( Munger( :Source Table, 14, 2 ) )

Re: MM/DD HH:MM conversion how to do

jthi — Thu, 29 Aug 2024 05:57:01 GMT

There are a lot of different methods of converting strings to datetimes in JMP. Building it from ground up by splitting it from parts is the method which has the highest success rate but there can be easier methods, such as Format Pattern (note that In Format() has << Use Locale argument which you might need sometimes)

Names Default To Here(1);

strs = {"20240207_163752", "20240208_132212", "20240320_180945", "20240321_062705"};

For Each({str}, strs,
	datetime = Informat(str, "Format Pattern", "<YYYY><MM><DD>_<hh24><mm><ss>");
	Write("\!N", str, " converted to time is ", datetime);
);

20240207_163752 converted to time is 07Feb2024:16:37:52
20240208_132212 converted to time is 08Feb2024:13:22:12
20240320_180945 converted to time is 20Mar2024:18:09:45
20240321_062705 converted to time is 21Mar2024:06:27:05

If using as formula replace str with your column

https://www.jmp.com/support/help/en/18.0/#page/jmp/datetime-functions-and-formats.shtml# - Scripting Guide

https://www.jmp.com/support/help/en/18.0/#page/jmp/date-and-time-functions.shtml#ww2531887 - JSL Syntax Reference