Can't get time values into ArcGIS 10

[AndyRitchie]

I have an excel file (xlsx) with the following fields

NavIDX ShipLat ShipLon DateUTC TimeUTC DateTimeUTC

I can read the file in ArcGIS just fine, and when I display xy data, all of the fields with time values come through.

But when I export to .shp, the UTCTime field goes to 12:00:00 AM and the DateTimeUT field reads only the date (C in the field name is truncated).

I tried a join with the table based on the NavIDX field, then calculating the TimeUTC field based on the TimeUTC field from the table, but all that does is freeze ArcGIS.

There are something like 224K records.

[AndyRitchie]

<sigh> Answered my own question again.

shapefiles can't store time unless it's a string. Time fields get truncated to day. If you create a geodatabase feature class from the xy data, all the date fields will come in fine. If you need them in a shapefile, export the feature class to a shapefile, delete the DateTimeUTC and TimeUTC fields, and re-create them as strings, then join the geodatabase feature class to the shapefile (I create an index field in case there are duplicate entries) and calculate the fields in the shapefile from the geodatabase. In detail:

1) get your NavData into the format you want it in excel. I put individual days into sheets, and combined them into a final nav sheet, but you could do it by day for long cruises.
1a) I add an index field to make sure that there are no hiccups from duplicate lat/long/time entries
2) open the excel table in ArcGIS, create xy event layer, export to GEODATABASE (all the date fields should come in ok)
3) export geodatabase to shapefile. This should break the TimeUTC and DateTime
4) delete the datetime and TimeUTC fields in the shapefile, and re-add them as text. The TimeUTC field should be 11 characters (XX:XX:XX AM) and DateTime should be 22 (XX/XX/XXXX XX:XX:XX PM)
5) join the geodatabase to the shapefile on the index field (you might be able to get away with Lat or Lon)
6) calculate the TimeUTC and DateTime fields with the field calculator from the date fields in the Geodatabase.

Andy