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

Question

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.

• Midavalo: Possible duplicate of How to parse serial date in ArcGIS?
• Vince: Strictly speaking,this isn't even a GIS question, since this is exclusively about using native date functions to generate a date. Listing three languages makes it three (maybe 2.5) different questions, so there's a number of potential reasons to close this question. The requirement for including code is not a manifestation of spite -- it really does make it easier to understand what is being asked if there's an example of the code to date, and then it's easier to generate an understandable answer.
• Vince: You first have to choose an environment (arcpy, field calculator,...), then a language. You may find that having to put together an earnest attempt to solve the problem will actually solve it.
• Paul: @pelampe, it's the same concept for a date and a date + time. In short, the integer portion of the serial number corresponds to the date while the fractional corresponds to the time. 40240 is a date, 0.625 is a time, and 40240.625 is a date + time (3/3/2010, 15:00:00, and 3/3/2010 15:00:00, respectively)
• pelampe: I have made some changes to the original post. Hopefully this will be going in the right direction.

Answer 1

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?

• pelampe: That did the trick! The only thing I had to change is to make sure that the serial date value is LONG INT and not TEXT or DOUBLE. But otherwise, it worked perfectly. THANKS so much for your help!

Answer 2

An alternative..Definitely not as eloquent.. but it's what I came up with to solve the same issue.. using Field Calculator and the following expression in python.

def convExcelDate(inp):
 inp = float(inp)
 Yearconv = str(1900+int(inp/365.25))
 DaysRemconv = inp-((int(inp/365.25))*365.25)
 Month = 1
 for M in [31,28,31,30,31,30,31,31,30,31,30,31]:
 if DaysRemconv > M:
 DaysRemconv = DaysRemconv- M
 Month = Month + 1
 returnVal = str(int(DaysRemconv))+'/'+str(Month) +'/'+ str(int(float(Yearconv)))
 return returnVal

and calling the field value:

convExcelDate(!YourFieldHere!)