Select to view content in your preferred language

Export to Excel Date/Time

6229
2
Jump to solution
07-08-2015 02:17 PM
EvanJoyes
Deactivated User

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.

0 Kudos
1 Solution

Accepted Solutions
ChrisSmith7
Honored 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
ChrisSmith7
Honored 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"

m_neer
by
Frequent 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.

0 Kudos