topic Re: Get date and time out of a text in a cell in Discussions

Get date and time out of a text in a cell

rlangsner — Sat, 10 Jun 2023 23:54:15 GMT

Hi,

I have a data set that reports the day and time within one cell in this format: "12.08.2022 15:46:43 GMT-0700" (the date format is dd.mm.yyyy). I would like to create a column for the dates and a column for the time (I am going to assume that the time is the local time and the GMT-0700 is just indicating that is where the local time. So for each cell, I would like two columns:

Column 1: In date format: dd.mm.yyyy

Column 2: In time format 15:46:43

The different approaches that I have tried dont' quite seem to work, and I am wondering if it is due to the period between the month/day/year values?

Thanks,

Robert

Using JMP 16.

Re: Get date and time out of a text in a cell

Craige_Hales — Thu, 15 Sep 2022 02:28:06 GMT

dt = New Table( "Untitled", Add Rows( 1 ), New Column( "stamp", Character, "Nominal", Set Values( {"12.08.2022 15:46:43 GMT-0700"} ) ) );

dt << New Column( "time", Format( "H:m:s" ), formula( Informat( Substr( stamp, 12, 8 ), "h:m:s" ) ) );
dt << New Column( "date", Format( "d/m/y" ), formula( Informat( Substr( stamp, 1, 10 ), "d/m/y" ) ) );
dt << New Column( "datetime1", Format( "d/m/y H:m:s" ), formula( Informat( Substr( stamp, 1, 19 ), "d/m/y H:m:s" ) ) );

dt << New Column( "datetime2", Format( "yyyy-mm-ddThh:mm:ss" ), formula( Informat( Substr( stamp, 1, 19 ), "d/m/y H:m:s" ) ) );

Column formulas extract dates, times, and datetimes.

Usually you'll want a datetime column, not separate date and time columns.

Edit: I'm not sure where the formats are documented; I make a tiny data table, use the column properties to choose/test the format, then save the table script to a script window to grab the format name.

Re: Get date and time out of a text in a cell

rlangsner — Thu, 15 Sep 2022 16:19:45 GMT

Thank you Craige. This worked exactly. You're right, I do want the time and date in one cell now that I think about it.

Re: Get date and time out of a text in a cell

vince_faller — Thu, 15 Sep 2022 17:20:11 GMT

If you get to the point where your local assumption isn't true. It might be worth putting the times in UTC.

Names default to here(1);
dt = New Table( "Untitled", Add Rows( 1 ), 
	New Column( "stamp", Character, "Nominal", Set Values( {
		"12.08.2022 15:46:43 GMT-0700", 
		"14.08.2022 15:46:43 GMT-0730" // just to show half hour time zones
	} ) ) 
);


dt << New Column("DT UTC", Format( "yyyy-mm-ddThh:mm:ss" ), formula( 
	timezone_offset = Num( Substr( :stamp, 24, 3 ) ) + Num( Substr( :stamp, 27, 2 ) ) / 60; // get the timezone offset
	Informat( Substr( :stamp, 1, 19 ), "d/m/y H:m:s" ) - timezone_offset * 3600; // turn from local to UTC
 ));