how to maintain excel "time" field in shapefile

[spi...@gmail.com]

Whenever I create a shapefile from an excel spreadsheet, my "time" data (e.g. hh:mm:ss 09:23:54 AM) is lost and all cells show up as "00:00:00". Does anyone know how to maintain the time data to use in ArcGIS?

Many thanks!

[GIS in Ecology]

Hi there, 

Thanks for the post. This is a common issue and it is to do with ArcGIS not recognising the autoformatting for time and date used by Excel. There are four possible ways round this. 

The first is to try importing your Excel sheet as a table using the specific Excel conversion tool in ArcGIS (which if I remember correctly is in the TOOLBOX window under CONVERSION TOOLS> EXCEL> FROM EXCEL). If this successfully adds a table that maintains your time and date, you can then use the MAKE XY EVENT layer tool to make your shapefile from this table.

The second is to save your Excel sheet as a tab delimited file using the SAVE AS option in Excel. This should strip out the autoformatting and you should then be able to add the data using the MAKE XY EVENT LAYER tool as normal. However, this may have problems due to the autoformatting in Excel and it may appear that you lose the time/date values (see the next option as to what is happening here if this happens).

The third is to manually strip the autoformatting for time and date from Excel. To do this, right click on the time column and then select CELL FORMAT. Set the cell format to text, and this will then be imported into ArcGIS as text. However, if you already have data in this column, it will convert it onto a string of numbers (which is a decimal fraction of 24 hours). You may be able to retain the original values and formatting for time if you create a new column, set its format to text, then copy your time data and PASTE SPECIAL is as VALUES into this new column.

The fourth option is to divide you time into separate columns for hours, minutes and seconds and then convert your time into a decimal hours. This will allow you to sort by time, and you will definitely not have any problems importing into ArcGIS (as it will treat is as a number). To do this, you can use the =HOUR(), =MINUTE() and =SECOND() functions in Excel to extract the appropriate values to their individual columns. You can do a similar thing with dates using the =DAY(), =MONTH() and =YEAR() Excel functions. The problem with this is that you may well want your time/date in GIS in a more typical time/date format.

The best option for you will depend on how your data are set up and what you wish to use the time field for in ArcGIS, but hopefully one of them will work for you. 

Excel autoformatting for time and date is a well known issue when it comes to using Excel formatted data in other software packages (including GIS and statistical packages such as R), and as a general rule, if you wish to use Excel data outside of Excel, always manually set the columns that will contain time and date to a Text format (rather than relying on the autoformatting function) before you enter any of the data. This is easy to forget, and if you do, the moment you enter anything that looks like a time or a date, it will automatically set the cell to its autoformatting option for time and date, and this autoformatting can be very difficult to get rid of without losing you data, or having it re-forrmatted in a way that you don't want it to be. It's one of the more annoying features of Excel for biologists, but I've never found a way to be able to turn it off.

Anyway, I hope one of these options works for you, and if it doesn't, just post back on this thread and we can see if anyone else has any other suggestions.

All the best,

Colin