Table to Excel and the Date Field

93
5
Jump to solution
3 weeks ago
Labels (1)
LakshmiSankaran
Occasional Contributor

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

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JoeBorgione
MVP Esteemed Contributor

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.   

can't wait to retire....

View solution in original post

5 Replies
jcarlson
MVP Regular Contributor

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?

- Josh Carlson
Kendall County GIS
JoeBorgione
MVP Esteemed Contributor

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...

can't wait to retire....
LakshmiSankaran
Occasional Contributor

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.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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.   

can't wait to retire....

View solution in original post

LakshmiSankaran
Occasional Contributor

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

Thanks

0 Kudos