topic Re: Date import from excel in Discussions

Date import from excel

Steffen_Bugge — Fri, 15 Sep 2023 19:58:25 GMT

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

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?

Re: Date import from excel

briancorcoran — Mon, 11 Sep 2023 17:00:30 GMT

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

Re: Date import from excel

Steffen_Bugge — Mon, 11 Sep 2023 20:39:19 GMT

Hi @briancorcoran ,

All machines use Windows 10 and JMP 17.1.

I will reach out to tech support, thanks!

Re: Date import from excel

Mark_Bailey — Sat, 16 Sep 2023 13:29:39 GMT

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.

Re: Date import from excel

WebDesignesCrow — Tue, 19 Sep 2023 04:01:07 GMT

Try this one if it helps.

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

Re: Date import from excel

Steffen_Bugge — Sat, 23 Sep 2023 06:15:19 GMT

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

Re: Date import from excel

jthi — Sat, 23 Sep 2023 16:58:58 GMT

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));

Re: Date import from excel

Mark_Bailey — Mon, 25 Sep 2023 13:45:19 GMT

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.

Re: Date import from excel

Steffen_Bugge — Tue, 26 Sep 2023 13:26:05 GMT

Thanks @jthi! Spot on!