Importing Excel Files Containing Mixed Date Formats into JMP results in missing values

[#1]

Hello!

I am currently facing an issue while importing an Excel file that contains a date column into JMP. This column includes two different formats: "DD/MM/YYYY" and "DD/MM/YYYY hh:mm". The "DD/MM/YYYY" format should be interpreted as midnight, i.e., "DD/MM/YYYY 00:00".

However, when I import the file into JMP, whether via script or the GUI, whatever import option used, JMP seems to import missing values for one of these two formats, depending which format is on the second row. Please refer to the attached Excel sample for a better understanding.

      

My objective is to automate the processing of multiple files using a script that can handle this inconsistency.

I would greatly appreciate any assistance or guidance you can provide. Thank you for your support.

Kind regards,

Florent

[#2]

You could for example import your data as characters and then fix it by using JSL or JMPs tools after. You should be able to do this by telling JMP that there are no headers and data starts on row 1

[#3]

It is not straight forward, but it works. 

Many thanks,
Florent

[#4]

I can not replicate your findings.  I am running on a Windows 11 system and imported your sample file under JMP 18, 16 and 14.  None of these imports read in missing values.  I did have to change the display format to "m/d/y h:m:s" for the times to be displayed.

What version of JMP are you using, and on what operating system.

[#5]

I use windows 11 and JMP18.0.1. I send you the jmp table that I have when I open the file with default setting.

Thanks,
Florent