How to convert a numeric column to a date column correctly?

Question

Hi,

I have a date column in the format "yyyymmdd". An example date looks like this: 20000111. When I import data from a csv into JMP, this column is treated as numeric, continuous. I set its format to be date-yyyymmdd and realized that it was treated as 20000111 seconds past 1904/1/1. I changed the column to character, nominal and tried to use informat() to convert it but just cannot get it to work. The script looks like this: Informat( :New_Date, "yyyymmdd" ) New_Date is the name of the column. I ended up with a blank column. Tried to convert New_Date to string using Char(New_Date). That returns dots. Cannot understand why such a simple task could not be completed. BTW, I don't know JSL and used the formula tool

Thanks!

txnelson · Answer

Here is an example showing you how to do your conversion

* Open the data table with your character date values
* Create a new column and give it the name Date                                 
* Open the Col Info dialog and set the format to "m/d/y"

* Open the Formula Editor for the new column and enterDate MDY(
	Num( Substr( :New Date, 6, 2 ) ),
	Num( Substr( :New Date, 9, 2 ) ),
	Num( Substr( :New Date, 1, 4 ) )
)

Change "New Date" to the name of the column that has the character version of the date

* The new column will then display the new numeric version of your date

LNitz · Answer

Dan, The trick with changing the character to numeric and then applying the date format is really encouraging.  Unfortunately, my date column jumped back to character when I hit Apply and OK.  The column remains as a left-justified  text field:   2021-08-24.

I would like to get JMP date numbers so that I can merge with other dated files.

One possible problem: The data was taken as "left(longdate,10)" strip off the time values. Would that cripple my transformation?

txnelson · Answer

Here is an example where the column values(in this case only one row is populated) are numeric and in the structure you have indicated.

To change this to a proper JMP date/time column here is how I would do it.

First open the Column Info dialog for the column in question

In able for JMP to recognize this using an Informat which will convert the 20001101 to a Date/Time value, the input column needs to be character, so change the Data Type to Character and click on Apply

Now go back to the open Col Info dialog box, and change the Data Type to Numeric, and select the Format, "yyyymmdd" from the list of formats. Once again, click on Apply

Note in the above image that the displayed value remains the same.  This is because the display format is "yyyymmdd".  Also notice the column has been changed back to Numeric.

To validate that it is a true JMP Date/Time column, you can change the display format to a different Date format.  So go back to the Col Info dialog box, and select a different Date format.  In my example, the "m/d/y" format is selected.  Then click on apply

As can be seen, the correct Date is displayed as 01/11/2000.

Now just click on OK, and the column is converted as you want.