Don't have a definitive resolution yet, but the problem and workaround is well understood..........
Client provided Excel data with dates in 2 columns. In Excel, these show as formatted dates; the cell format is also defined as a regular date.
However, on import, all the date fields come in as 00/00/0000. Changing the field type resolved to a 5 digit number (so apparently NOT the numeric equivalent of date from 1/1/1900). The rationale behind the number has not been ferreted out as yet.
SO, just for grins and giggles, opened the Excel sheet in Apple Numbers. The dates were all 00/00/0000. Pasting the dates from Excel into Numbers, and saving as Excel, corrected the issue through whatever this obscure translation performed.
Digging into where the data source is, the client gets IT to export data from their EHR system. I suspect (a guess at this point) this gets exported as JSON, and JSON has a known issue with the format of dates. OData also has some idiosyncrasies relative to dates as well. Still some research to do, as I have no direct access to the client IT organization.
The amount of time we developers loose over the date issue when importing from Excel into Filemaker must run into the hundreds of hours.
Before I touch an Excel for import, the first thing I do is check the format at the column level (number, text, date etc). If that is a mess, I send it back to the client with comments to do some basic Excel training first.
I learned the hard way. I had to import massive Excel files (hundreds of columns 10.000's of rows and somewhere deepdown there would be a hickup on a date issue. That is when I first realized MS choose 1-1-1900 as the beginning of time
Excel shows a date, both in content and format. However if you change it to text, it is a 5 digit number.
Apple Numbers reads it as a date formatted field but the 5 digit number doesn’t resolve as a valid date but a date of all zeros. FileMaker does the same.
Other Excel clones show a date format and visually a date but the real data is something else. Idk how Excel is parsing this 5 digit number as a valid date presentation.
Whatever was going on in the source data, seems to be resolved. The latest import set provides has correctly formatted dates.
Although not relevant to this specific problem, most software - FM, Excel, Numbers, etc. - deals with dates as numbers, a little research discovered:
Excel cannot represent dates prior to 1/1/1900 as numbers or dates, but only as text.
Apple Numbers, based on a 1/1/1904 (avoiding a leap year issue?) represents prior dates as negative numbers.
FileMaker uses a base date of 1/1/0001
=========== UPDATE ============
Narrowing down the issue. The number in Excel is indeed the numeric equivalent in Microsoft's date offset calculation, and reflects the numeric correctly when one formats the field as either text or number.
The problem seems to be in some aspect of the field, and it's importing it into non-Microsoft products e.g. Apple Number, FileMaker, SPSS.
IDK at this point, if this is an issue from the latest version of Excel, or some other issue. Why it imports in as 0/0/0000 is the open question. Still investigating......and the original source of the data remains to be discovered.....
