Export to Excel Date/Time

4605
2
Jump to solution
07-08-2015 02:17 PM
Highlighted
New Contributor

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.

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Frequent Contributor

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"

View solution in original post

2 Replies
Highlighted
Frequent Contributor

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"

View solution in original post

Highlighted
by
Occasional Contributor

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.

Reply
0 Kudos