topic Re: Convert character time to numeric continuous time in Discussions

Convert character time to numeric continuous time

shasheminassab — Sat, 10 Jun 2023 23:23:01 GMT

I have a dataset that the time column is in this format "Mon, 30 Nov 2020 21:42:26 GMT" and JMP recognizes that as a character data type. Is there a quick way to convert this column to numeric continuous type?

Re: Convert character time to numeric continuous time

txnelson — Mon, 30 Nov 2020 22:34:35 GMT

Here is a little script that will create a new column with the converted value. Please note that I am assuming your current column with the character date in it is called "Date". Please change the script to what the real column's name is.

dt = Current Data Table();

dt << New Column( "Date Time",
	Format( "ddMonyyyy h:m:s", 22, 0 ),
	Input Format( "ddMonyyyy h:m:s", 0 ),
	,
	formula(
		Informat(
			Word( 2, :Date, ", :" ) || Word( 3, :Date, ", :" ) ||
			Word( 4, :Date, ", :" ) || " " ||
			Word( 5, :Date, ", :" ) || ":" ||
			Word( 5, :Date, ", :" ) || ":" ||
			Word( 5, :Date, ", :" ),
			"ddMonyyy h:m:s"
		)
	)
);
dt:Date Time << delete formula;

Re: Convert character time to numeric continuous time

Mark_Bailey — Tue, 01 Dec 2020 14:13:32 GMT

You can interactively make the change. Select the data column. Select Cols > Column Info. Change the Data Type to Numeric. Change the Format to the matching Date format of your character strings. You do not need a new column this way.

Re: Convert character time to numeric continuous time

Craige_Hales — Tue, 01 Dec 2020 15:08:05 GMT

If you use Mark's approach, you'll need to remove the extra bits first. I used a regex in the find/replace dialog:

You have to run it twice. The first time removes the day and the second the GMT.

The regex matches three letters followed by a comma and a space, or it matches a space followed by GMT.

Then use

Change DataType *and* Format at the *same* time or you'll get missing values.

If you use Jim's approach, I think the 5-5-5 at the end should be 5-6-7.

Re: Convert character time to numeric continuous time

shasheminassab — Tue, 01 Dec 2020 15:30:33 GMT

Thank you @Craige_Hales @Mark_Bailey @txnelson. Very helpful!!

Re: Convert character time to numeric continuous time

markschahl — Tue, 01 Dec 2020 19:25:47 GMT

If @Mark_Bailey approach doesn't work, I use this addin by @brady_brady : Data Table Tools

Data Table Tools > Special Formula Columns > Custom Date Formula Writer.

Re: Convert character time to numeric continuous time

shasheminassab — Tue, 01 Dec 2020 19:32:11 GMT

Thanks! yes, I have used that add-in and it is really helpful.

Re: Convert character time to numeric continuous time

brady_brady — Tue, 01 Dec 2020 20:11:46 GMT

This should also work (in a new column formula), since for the format you've provided, there are always 5 leading and 4 trailing characters to remove. As above, you can format as desired afterward by using the column property dialog.

Note: In the formula below, the source column's name is C1; please change as is appropriate for your table.

Informat( Substr( :C1, 6, Length( :C1 ) - 9 ) )