Export to Excel Date/Time

EvanJoyes

Is there an easy way to export a table from ArcGIS with a column containing the date time in the format MM/DD/YYYY 00:00:00 AM to excel and have it open in excel as the same date time format? I've tried formatting the cells in excel but it loses the time data and will only show the date.

ChrisSmith7

Try exporting as a flat file (.txt) and then opening from within a blank Excel doc. From here, you can specify your delimiters, then format the date field as you wish.

So, from within ArcMap, open your feature table, export, select "text" as type, then save. Side note... shapefiles and coverages will truncate time, so use a gdb table/fc... Once you have the .txt, you can open and see the data exported correctly, e.g.:

"OBJECTID","Date_Field"

1,1/2/1985 13:00:56

From here:

1) Open a blank Excel doc

2) Open the file from within Excel (make sure you select "All files", not just "All Excel files", otherwise your file will not show-up in the list)

3) In the text import wizard, choose "delimited"

4) Check "My data has headers" if it does

5) Next

6) Choose "Comma" as the delimiter

7) Uncheck "Tab"

😎 Choose your text qualifier (" is probably fine for you)

9) Next

10) Assign each fields' format

11) On your date field, choose "MDY"

12) Finish

After the excel doc opens with your data, you can format it even more under "Custom" in the Excel fields' "Format Cells" window , for example:

mm/dd/yyyy hh:mm:ss AM/PM

This will preserve your original format requirement "MM/DD/YYYY 00:00:00 AM"