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; } } }

Changing Date Format

kschnarrs

I'm trying to import data from an excel file where a column contains dates in a format that looks like "Monday, December 05, 2016, 2016 12:20:01 PM EST"

I want to plot this data in JMP in chronological order. To do so, I believe I need to get the date into numeric continuous format. Does anyone know how to convert the date over? I've seen a couple date conversion in JMP, but non starting from the format that I'm starting with... I don't need the time the to be listed, just the date.

Thanks in advance!

Accepted answers

txnelson

The code I specified is to be used as a formula for a new column.
All that is being done in the formula, is the building of a string that should look like,
ddMonyyyy hh:mm:ss
in structure. The Word function returns the 1st, 2nd, etc. word, using the delimiters specified as the word separators. In the example, I am using both a blank and a comma as the separators to look for. So you should be able to verify that what the original column looks like and to map it to what the formula is creating. you could paste in a screen dump of your data table, that would help.

All comments

Mark_Bailey

I assume that the data is currently character data type in JMP after you get it over from Excel or you wouldn't be asking this question.

Select the column and then select Cols > Column Info. Change the data type to Numeric. Change the Format to the one that matches your character string.I think it is the Long Date format.

kschnarrs

When I do that, the output becomes blank because the input format doesn't exactly match any of the input options (dates or times) since the input has the day of the week as well as "AM/PM" and "EST."

Mark_Bailey

Sorry about that!

My next attempt use a column formula also failed but I will post it so someone else does not waste their time.on it.

t1 = Words( :Charater Date );
t1 = t1[1] || " " || t1[2] || " " || t1[3] || " " || t1[4]
 || " " || t1[6] || " " || t1[7];
As Date( :Charater Date, "Long Date" ); // or Informat()

The idea was to isolate the words making up the string, concatenate the good ones, and then parse it. I'm afraid that I will have to leave it to someone who is more familiar with date formats and parsing character strings.

txnelson

Here is a formula that works for a character string column called "Date". Just copy this formula to a new column, name the column whatever you want to name it, change the format to whatever date or time format you want, and then run the formula.

TheDate = Informat(
	Word( 3, :date, " ," ) || Substr( Word( 2, :date, " ," ), 1, 3 ) || Word( 4, :date, " ,:" ) || " " ||
	Word( 6, :date, " ," )
);
If( Word( -1, :date, " " ) == "PM",
	Thedate = thedate + 43200
);
thedate;

Steven_Moore

I used to have the same problem. The easiest solution for me was to change the Excel date column from "General" (or whatever) to "Short Date". Then copy/paste or import intoJMP. Works every time!

kschnarrs

I'm having trouble doing that - since the input date format doesn't match something that excel recognizes, it won't convert it over to short date.