XLS data in the format DMY appear in JMP as MDY after import. How to avoid?

Question

Hi,

I'am struggling to get the porpper format for the date. In xls the column has the format DMY - see example above and I end up with the JMP format MDY. Trying to change the format in the column info window ends with a mess:

Date_example.xlsx

gzmorgan0 · Accepted Answer

Try this:

* Open the file in JMP.
* Right click on column Process Start Date select Info and change the data type to character, OK
* Right click on column Process Start Date and change to Numeric, continuous, and select date for format,
* select d.m.y  then two items will apper in teh info window Format and Input Format. Input format must be d.m.y and you can select any other date formate for Format.

Since I am in the US, the Process Start Date with the periods (.) is read in as character and our format uses slash(/). This worked for me in both JMP14 and JMP 13.

PS_Ato · Accepted Answer

Hi,

this script based on the given solution (see above) worked fine

// Set data/modelling type for "Process Start Date".

:name("PROCESS Start Date") << data type ("character");
:name("PROCESS Start Date") << data type (Numeric, date, Format("d.m.y"), Input Format("d.m.y"));
//optional
:name("PROCESS Start Date") << modeling type ("ordinal");