Date import from excel

[#1]

I have imported the following data from excel to JMP on several different computers using the same script

30/11/2022 12:15

14/12/2022 12:15

12/12/2022 12:15

11/01/2023 12:15

11/01/2023 12:15

On some computers, JMP gives the following:

30.11.2022 12:15
14.12.2022 12:15
12.12.2022 12:15
11.01.2023 12:15
11.01.2023 12:15

While others produce this results:

44895,5104166667
44909,5104166667
44907,5104166667
44937,5104166667
44937,5104166667

And I am not able to convert these numbers back to the correct dates in JMP. Any idea why this is happening and what to do to avoid it?

[#2]

Hi Steffen,

You don't mention what operating system (Windows or Mac), version of JMP or language you are running under for the various machines.  Are they different?  All of these factors can affect how the date data is interpreted.  I would suggest contacting JMP tech support.

Brian Corcoran

JMP Development

[#3]

Hi @briancorcoran , 

All machines use Windows 10 and JMP 17.1. 

I will reach out to tech support, thanks! 

[#4]

Try this one if it helps.

https://community.jmp.com/t5/Discussions/Convert-Date-after-Import-file-from-Excel-Issue/m-p/653497

[#5]

BTW, the second set of results is likely correct. JMP represents date and time values as the number of seconds since midnight on January 1, 1904. The second set didn't change the Format attribute for that data column. Select Cols > Column Info and apply a date format to see if the values are correct. Your script can apply this format automatically after you import the data.

[#6]

Thanks for the input @Mark_Bailey 
When changing to a date format, this is the result:

01.01.1904 12:28
01.01.1904 12:28
01.01.1904 12:28
01.01.1904 12:28
01.01.1904 12:28

[#7 · Accepted solution]

DATEVALUE function (support.microsoft.com) 

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

Excel also has a nice feature where it assumes that 1900 is a leap year which might affect your calculations if you try to perform the conversion by hand Excel incorrectly assumes that the year 1900 is a leap year 

If I had to guess the different computers are using different date (or time) settings (either in JMP and/or Windows). If you are able to load those dates as characters, you might be able to fix them before they break

Or something this might or might not work

excel_datetime = 44895.5104166667;
As date(In Days(excel_datetime) + Date DMY(30, 12, 1899));

[#8]

Thanks @jthi! Spot on! 

[#9]

The second set of results appears to be the number of seconds since midnight. The date seems to be lost. It appears to be a duration instead of a date and time.