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.
Solved! Go to Solution.
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"
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"
You can create another column in your feature class layer that is "text" formatted, then calculate your date column into this new field.
When you export it now as a table, the format you want is there.