topic Date field in Excel to Table tool in ArcGIS Pro Questions

Date field in Excel to Table tool

JoeBorgione — Wed, 03 Apr 2019 20:23:01 GMT

I was given a couple of excel spreadsheets to geocode and have just realized that there is a date field in the spreadsheets that got converted to a text field with the excel to table tool (ArcGIS Pro 2.3.1). Is there any correlation between the string values and a real date?

For example: Excel date 5/8/2003 = 37747 Pro string and Excel date 6/9/2003 = 37777 Pro String....

Re: Date field in Excel to Table tool

JoeBorgione — Wed, 03 Apr 2019 20:53:41 GMT

Just found this: https://community.esri.com/thread/174953. Scratching head now.....

edited to add:

The above link gave way to some good information:

First off the numbers I mention in the initial thread DO have a direct correlation to a real date. They are known as Date Serial Numbers and from https://support.office.com/en-us/article/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252 :

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.

The excel to table brings that serial number in as a string, so in the field calculator, I insert:

datetime.datetime(1899,12,30) + datetime.timedelta(days = int( !The_Field_Name!))

the int() function is the secret...

In my case I have a START_DATE and END_DATE field I'll need to take care of:

Be sure to vote early and vote often: https://community.esri.com/ideas/16476-excel-to-table-date-conversion

Re: Date field in Excel to Table tool

RenRosin — Wed, 14 Oct 2020 16:04:53 GMT

I've been struggling with this issue for months and months, and was delighted to see your post ... went to try it, and it seems that the in newest version on Pro the date comes in as a date! Hooray!

Re: Date field in Excel to Table tool

Nicolas_ — Wed, 06 Dec 2023 17:09:58 GMT

Starting with what version?