Subscribe Bookmark RSS Feed

Changing Date Format

kschnarrs

Community Trekker

Joined:

Feb 1, 2016

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution
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.
Jim
9 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Feb 1, 2016

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

markbailey

Staff

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 22, 2012

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;
Jim
kschnarrs

Community Trekker

Joined:

Feb 1, 2016

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

 

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution
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.
Jim
kschnarrs

Community Trekker

Joined:

Feb 1, 2016

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

Super User

Joined:

Jun 4, 2014

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!

Steve
kschnarrs

Community Trekker

Joined:

Feb 1, 2016

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.