Table to Excel and the Date Field

[LakshmiSankaran]

We just noticed that when exporting an (ArcSDE 10.7.1) feature class table to excel, using the Geoprocessing Tool Table to Excel, all date attributes converted correctly except dates that were entered as 1/1/1800. Those errored out as a negative number -36523. This was true using ArcCatalog 10.7.1 or ArcGIS Pro 2.7.3.

Any reason why?

Thanks

Lakshmi Sankaran

[jcarlson]

Dates are kind of funny, in that they are stored as integers but interpreted as dates when being accessed. To further muddy the waters, Excel doesn't always do a great job of intuiting the value of a date field (or any field, for that matter).

Depending on the underlying database, the "0" date can be all sorts of things. Can you clarify what DB platform the SDE is running on?

Also, can you try exporting to a text-based format and looking at the raw values before Excel tries to interpret them?

[JoeBorgione]

I had a problem going from excel to an ArcGIS table and the weird date serial microsoft uses. Follow the links in that post as they might explain the error; I don't remember what date 0 is in the microsoft calendar.

As @jcarlson mentions, dates can be funny, but not in a haha sort of way...

[LakshmiSankaran]

Thank you for your response.

SDE is running on SQL Server 2016 SP2, (so that makes a date-time field).

A text export shows 1/1/1800 correctly, viewed in Notepad, WordPad or CSVed.

The xls is fine with "10/1/2000" and "11/1/2000", just not 1/1/1800.

[JoeBorgione · Accepted solution]

From the linked post:

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.

So your 1/1/1800 is a negative number in the microsoft serial calendar although it's hard saying why it converts to -36523 and not just -365. You may need to change those dates to1/1/1900 and see if it works out for you.   

[LakshmiSankaran]

Yes saw that post and that works. I guess for historical data one would need to use the txt export. Good to know.

Thanks