nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Convert character time to numeric continuous time

shasheminassab

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?

Accepted answers

All comments

txnelson

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;

Mark_Bailey

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.

Craige_Hales

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.

shasheminassab

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

markschahl

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.

shasheminassab

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

brady_brady

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