I am trying to format a date field that underwent incorrect importation. The result was that the date format became coded in a way i am unsure of.

Is there a simple way using the field calculator to batch process date formats?

In the meantime, I will try to create a unique ID in order to join the two tables.

Here is what it currently is:
enter image description here

This is the format I want it in:
enter image description here


I believe these dates come from Excel and represent days since Jan 1, 1900.

From the Office Help:

Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. 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. You will need to change the number format (Format Cells) in order to display a proper date.

You could use Field Calculator with Python code to calculate the new date field:


import datetime

def calc_date(field):
  return '{}'.format(datetime.date(1900, 1, 1) + datetime.timedelta(int( field )))


calc_date( !IntDate! )

The 38184 will thus become 2004-07-18

And here is a solution you can run outside of Field Calculator (from your Python IDE or ArcMap Python window):

import arcpy
import datetime
fc = r'C:\ArcGIS\Default.gdb\_fishnet'

with arcpy.da.UpdateCursor(fc,['IntDate','RealDate']) as cur:
    for row in cur:
        row[1] = '{}'.format(datetime.date(1900, 1, 1) + datetime.timedelta(int( row[0] )))
  • For the Field Calculator route, am I to create a new field (float style)? Or use the field calc. on the existing field which I want to re-format? When I do run the Field Calculator, am I replacing your {} with the range of rows? And field with the name of the intended field? I am still new at python script. – Sebastian.D Aug 10 '16 at 20:40
  • 2
    @Sebastian.D For the field calculator, create a new field of type 'Date'. The {} part is part of the string formatting and no you do not change it. Use the expression as it is written, just replace !IntDate! with your field name. – jbalk Aug 11 '16 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.