nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Date field in Excel to Table tool

JoeBorgione

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

Trouver plus de publications taguées avec

Accepted answers

RenRosin

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!

All comments

Nicolas_

Starting with what version?

RenRosin

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!

JoeBorgione

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

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.