I am trying to import a file from Excel which is a GPS data set of consecutive points at 15 minute intervals. When I import the file and save it as a shapefile the time column is not read properly and GIS converts to 00:00:00 and then further down the column shifts to 01/01/1900.

I have tried to format the column in Excel as either hh:mm, hh:mm:ss, dd/mm/yyyy hh:mm or no number format and each has the same end result. The imported table shows the correct times but as soon as the data table is converted to a shapefile to be displayed the time format is lost.

I have never noticeably had this problem when importing data previously and I did not need to format the time. Now it seems that no formatting method will work properly.

How can I format the time column to retain the time values when imported and saved as a shapefile?

  • Have you tried to create a shapefile first, add a field of type date, join your excel data to the shape and then use the calculate field-method to get your date in a proper format?
    – LarsVegas
    Jun 13, 2012 at 14:45
  • Just joining the excel sheet to the shapefile allows me to view the time for each point yet there must be a way of formatting the excel column which allows Arc to read the column as a time?
    – James
    Jun 13, 2012 at 14:58
  • Have you tried what I suggested? When importing from dbf or excel you encounter all sorts of problems. Of which type is field after the join?
    – LarsVegas
    Jun 13, 2012 at 15:30

5 Answers 5


The reason that you are not able to save Time related information in a shapefile is that the Shapefile format, does not support Time as an attribute. They only support Date fields. This is due to the fact that the shapefile uses an older specification of the dBase file (.dbf) to store the attribute table.

If you need to store time data, you are going to have to use one of the ESRI Geodatabase formats, either the Personal GDB, or the File GDB, for local storage. Even with these formats, there are limitations on how well Time attribute fields are supported. This ESRI Help article discusses SQL query expressions, but also mentions what storage types support Time Attributes: SQL Reference for query expressions used in ArcGIS

Edit - To directly address your question of how to maintain your Time data.

When you save data as a specific time format, for example, 12:30:05 PM, this is stored, according to the SQL link above, as 1899-12-30 12:30:05. This is why you are seeing a conversion in the time between Excel and the shapefile.

If you have been able to do this in the past, what was likely occurring, was that the cells containing your time data were not actually formatted as "Date/Time" in Excel, therefore, when converted to Shapefile, there was no conversion of the times, and they were saved to a String field.

If you want to convert Time data from Excel, and store it in a shapefile, you will need to save it to a Text field. This Help article discusses Supported Field formats, and discusses storing date/time as both a string or a number.

This next article discusses Converting string or numeric time values into date format

These articles should provide you with some workarounds if you have to maintain your data in the Shapefile format.

  • I have never had this issue before when importing excel files with a time column. I can't understand why it would have worked previously or what the format was which allowed the time column to remain unchanged.
    – James
    Jun 13, 2012 at 15:57
  • @James - See edit to answer for clarification. The cell format in excel was likely "text", which was then saved directly to a text field in the shapefile. This would mean no conversion was necessary. Jun 13, 2012 at 16:19
  • 3
    (+1) For many purposes, a more effective alternative to importing times from Excel is to put them in numeric format: they correspond to (fractional) numbers of days from a standard time origin (the end of the 1800's). This makes them immediately available for meaningful graphing, mapping, and statistical analysis. It's also easy to recover the times by means of arithmetical field calculations, if you really want them in base 2+12+60+60 format instead of decimal format!
    – whuber
    Jun 13, 2012 at 16:20

I found the following method to work (after many attempts of trial and error).

I stored my data in an excel spreadsheet (v. 2010) for convenience and ease of editing. Format the column that contains the time data as "General". Enter the current date (for example today is 02/11/2012) followed by your specified time. So, each cell should contain something like 02/11/201209:00:00 (without any spaces).

I then saved the spreadsheet as a .csv and imported it into ArcMap. Display the XY coordinates as usual then export the data as .shp file.

The time field should now read exactly as it does in the excel spreadsheet, and function in a logical way.


If you don't have to do any calculations using the time attributes in ArcMap you can create separate columns extracting the Hour, Minute and Seconds of the time in Excel and then re-concatenate them in Excel later if you will be doing time-based analyses. I've been doing this and it's been working well for my purposes although not useful if you need to do calculations with the time in ArcMap.


Copy column to Notepad. Format column in Excel to Text. Copy back from Notepad into Excel and Paste Special as Text.



Creating the table in arc first with the proper typing of the fields, and then appending from the excel worksheet can work as well (make sure to drop the spreadsheet from your map session, as the lock will terminate the append...). As a quick way of doing this, one can create the table or feature class in a file geodatabase, and choose the import fields option; then export to shapefile if that is really your preferred destination format.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.