Converting Excel integer date format to date using ArcGIS

Question

Similar question as this post: Converting Excel integer date format to datetime using QGIS, but for ArcGIS.

I have a field with integer values like 45118 which came from a column in Excel containing dates which was then converted into an ArcGIS table. I want to convert these values back to dates in ArcGIS. For example, I want to convert 45118 to 7/11/2023.

I'm using ArcGIS Desktop 10.8 and/or ArcGIS Pro 3.1.

Answer 1

Looking at What's new in ArcGIS Pro 3.2- ArcGIS Pro | Documentation:

New field data types

Four new field data types are available:

• Big integer—Supports 64-bit data. Stores whole numbers that exceed the range of -2.14 billion to 2.14 billion.
• Timestamp offset—Stores date and time values and can include a coordinated universal time (UTC) offset, such as 5/16/2023 10:00:00 AM -07:00.
• Date only—Stores a date value only, such as 2023年04月12日.
• Time only—Stores a time value only, such as 10:00 AM.

and

• You can migrate Date fields in a table or feature class in a geodatabase to high precision, allowing them to support time with millisecond values.

Starting with ArcGIS Pro 3.2, there are now 5 date & time data types: Date, Date (High precision), Date only, Time only, and Timestamp offset. Although the Excel date integer conversion still works, it doesn't work the same for all 5 data types.

import tempfile
from arcpy.management import *
from arcpy.da import *
# all supported ArcGIS DateTime fields
date_fields = (
 "DT DATE",
 "DTH DATEHIGHPRECISION",
 "DO DATEONLY",
 "TO TIMEONLY",
 "TSO TIMESTAMPOFFSET"
)
# Create FGDB in temporary directory
ws = tempfile.mkdtemp()
fgdb = CreateFileGDB(ws, "fgdb")
# Create tables and add new date & time fields
tbl = CreateTable(fgdb, "TableA")
res = AddFields(tbl, ";".join(date_fields))
# Add record and Calculate Fields using integer date representation
_ = InsertCursor(tbl, "ObjectID").insertRow([None])
for field in date_fields:
 name, dtype = field.split()
 res = CalculateField(tbl, name, 45118)
# print results
with SearchCursor(tbl, "*") as cur:
 row = next(cur)
 for field, value in zip(("ObjectID ObjectID",) + date_fields, row):
 print(f"{field.split()[1]}\t{value}".expandtabs(20))

results in:

ObjectID 1
DATE 2023年07月11日 00:00:00
DATEHIGHPRECISION 2023年07月11日 00:00:00
DATEONLY 2023年07月11日
TIMEONLY 00:00:00
TIMESTAMPOFFSET None

• Taras: Have never used this package tempfile before, that you for a great idea 😉

Answer 2

If the source field is numeric, to calculate a date value in a date field from that, simply use Field Calculator with the expression being the source field value and that's it.

Initially this did not work for me, because my source field was string. So in my Field Calculator expression I converted the string to integer using Python, like int(!source_field!) and this did what I needed.