[GIS] Converting 5 digit serial date to mm/dd/yyyy using VBScript in ArcMap Field Calculator

arcgis-10.2arcgis-desktopfield-calculatorvbscript

I have imported a table from Excel that has one field (StartDate) to hold dates. When I imported the table it into ArcMap, the date values in that field changed from DD/MM/YYYY to a 5-digit number, e.g. 3/3/2010 now shows as 40240. In ArcCatalog, upon inspection the properties of this field is now a string data type. This 5 digit number is a result of stripping away the Excel formatting and revealing the true absolute, or serial date value of this particular date.

Therefore, I want to use the field calculator to convert the 5-digit string values back into a readable date format, using VB.

I have created a new field (StartDateDT) (formatted to DATE) to house the converted (5-digit to Date) date values.

Using ArcMap 10.2.2 Standard license.

Best Answer

Open the Field Calculator, set to use Python Parser, and enter the following expression:

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

enter image description here

There is potential if you have dates between 1 Jan 1900 and 1 March 1900 that this will give an incorrect value due to Excel thinking 1900 was a leap year, but for anything after those dates this should work.

It is possible to work around the above bug, but if you don't have those dates then I wouldn't bother.

Credit: Code for this answer taken from Parsing serial date in ArcGIS for Desktop?

Related Question