topic Re: Changing Date Format in Discussions

Changing Date Format

kschnarrs — Mon, 05 Dec 2016 19:36:16 GMT

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!

Re: Changing Date Format

Mark_Bailey — Mon, 05 Dec 2016 19:53:01 GMT

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.

Re: Changing Date Format

kschnarrs — Mon, 05 Dec 2016 19:54:56 GMT

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."

Re: Changing Date Format

Mark_Bailey — Mon, 05 Dec 2016 20:27:04 GMT

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.

Re: Changing Date Format

txnelson — Mon, 05 Dec 2016 20:38:07 GMT

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;

Re: Changing Date Format

kschnarrs — Mon, 05 Dec 2016 21:38:23 GMT

I can't get that to work. I'm not sure what I'm doing wrong (I don't have a ton of experience with coding). Do you mean for me to create a separate script and paste that in? Or to go to Cols -> New Column -> Formula and paste it as a formula? I tried both but coudln't get it to work right...

The column with the dates is titled "Date Acquired" so I replaced that everywhere you had 'date,' leaving everything else the same and tried to create a New Column just titled "Date" for it to go under..

Re: Changing Date Format

txnelson — Mon, 05 Dec 2016 22:00:24 GMT

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.

Re: Changing Date Format

kschnarrs — Mon, 05 Dec 2016 22:10:41 GMT

I got it - I think I had forgotten to set the output format correctly so that's why I was having trouble. Thanks!

Re: Changing Date Format

Steven_Moore — Tue, 06 Dec 2016 14:25:02 GMT

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!

Re: Changing Date Format

kschnarrs — Tue, 06 Dec 2016 15:11:03 GMT

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.