Convert Date after Import file from Excel Issue

[#1]

I  think many users use Excel to keep their data because not everyone in the organization has the access to the company database.

I observed that, the date did not import correctly if there is missing data in between rows in the date column (does it that mean JMP cannot handle text and numeric value in the same column?). 

To recap few discussions about date format return to 01/01/1904 after Excel import (Import from Excel -Date as Character issue):

1) https://community.jmp.com/t5/Discussions/Need-help-converting-a-date-format-from-character-to-numer...
2) https://community.jmp.com/t5/Discussions/probl%C3%A8me-importation-date-%C3%A0-partir-d-excel/m-p/60...

Example: 

I want to convert to convert numeric 45008.078125 (JMP numerical value after import from Excel) to 3/23/2023 1:52:30 AM (as per actual data in Excel)

Initial solution that I received from JMP is to install add-in which can be found at below link;

https://community.jmp.com/t5/JMP-Add-Ins/Data-Table-Tools-Add-in/tac-p/653491#M1772 

(However, the add-in cannot be used for this case, so it was suggested to use date formula & I need to edit the formula with minus 2days to get the correct date)

Steps:

1) Format the Date column from Character to Numeric

2) Create new Column formula as below;

As Date( In Days( :Diebond End Date JMPImport) + Informat ("1/1/1900", "<MM></><D></><YYYY><hh><:><mm><:><ss><ampm>")) - In Days( 2 )

The JMP formula suppose to use 01/Jan/1900 but somehow it exceed by 2 days. Why is that so?

3) Set the format pattern to the desirable format

I set format pattern: <MM></><D></><YYYY><hh><:><mm><:><ss><ampm>

Example of JMP file in the attachment (5 empty cells in Diebond End Date Excel)

I'm using JMP 17.0.0.

Not sure if JMP 17.1 has resolved this issue.

[#2 · Accepted solution]

Hello @WebDesignesCrow , (I've also posted this on the addin page)

JMP's calculations are correct; for example, 45008 days from 0000, 01 Jan 1900 is 0000, 25 March 2023--not 23 March 2023.

Here is why: 

The years 1900 - 2019, inclusive, represent 30 "blocks" of 4 years each. Each "block" contains (366 + 365 + 365 + 365) = 1461 days, EXCEPT the block with year 1900. 1900 is not a leap year, because if a year is divisible by 100, but not 400, the year is not a leap year.

So we have 1461 * 30  - 1 = 43829 days in the years 1900 - 2019 inclusive.

2020, 2021, and 2022 add (366 + 365 + 365) days.

Jan, Feb of 2023 add (31 + 28) days.

March of 2023 adds 24 days to get us to 0000 on the 25th.

This total is 43829 + 366 + 365 + 365 + 31 + 28 + 24 = 45008. Any remaining decimal portion is what gives the hours, minutes and seconds on 25 March 2023.

I'm not sure what is being done in Excel, but here are some common errors when computing date ranges manually (as may be the case with the Excel file?)

a) Treating all years divisible by 100 as leap years.

b) Failing to treat years divisible by 400 as leap years.

c) Counting too few, or too many, days due to misusing endpoints. For example,

  -- 1 day since midnight 01 Jan is 02 Jan, not 01 Jan.

  -- The 20th - 24th inclusive represent 5, not 24-20 = 4 days... etc.

I hope this helps,

Brady

[#3 · Accepted solution]

Microsoft Excel has left 1900 as leap year (because it was like that on Lotus 1-2-3) on purpose as it would break too many things. You can read a bit more from Excel incorrectly assumes that the year 1900 is a leap year (learn.microsoft.com) 

[#4]

Aha!

Thanks a lot for the explanation @jthi 

[#5 · Accepted solution]

You asked, "Does it mean that JMP cannot handle text and numeric values in the same column?"

That is correct. Excel is a spreadsheet application. A spreadsheet is based on cells. JMP is a statistical application. It is based on a data table with variables (columns) and observations (rows). A variable can have only one data type and modeling type.