AnsweredAssumed Answered

Dealing with "Time" formats from EXCEL to ESRI

Question asked by Dcamrine on Feb 10, 2015
Latest reply on Mar 31, 2015 by vangelo-esristaff

GeoNet Community,


I have a feeling this is a simple fix but i have spent all morning trying to convert a text field containing a date into a date field.


In Excel you change the format of the field and it changes how the date is displayed. To make it "permanent" you either have to convert the time value into a serial code or I used =A1 in the neighboring field to lock in what ever format i had selected into a new field.


Now when trying to get this into ESRI it becomes very tricky. When i use "Add XY data" and open the "Event" layer I can open the table and the date fields all display the way it looks in EXCEL, as soon as I export the event layer to a new Feature Class it changes the field into the "Date" format and cuts off the HH:mm:ss


To preserve the loss of the HH:mm:ss I created a separate field for year, month, day, hour, minute, and second in Excel as text columns and these are preserved in the new feature class. This is a very time consuming process in Excel and it seems there should be an easier way.


Using the "Convert Time Field" Tool:


The original date text field looks like the following:

6/15/2011 7:58:42 and it's definitely a string column.


When I use the tool I went to custom format and entered several options and non of them worked. I tried:


MM/dd/yyyy HH:mm:ss

m/dd/yyyy HH:mm:ss

m/dd/yyyy h:mm:ss


I converted all single digit values into 2 digit values and I used the following VBscript expression to use the separate text columns and combine them into a single text column in ESRI acceptable format.


TimeText2 = [YEAR]&"/"& [MONTH2]&"/"& [DAY]&" "& [HOUR2]&":"& [MIN2]&":"& [SEC2]


I ran the tool again and the new field only showed the Year\month\day


What is going on?




Any help on this is much appreciated! I'm using ArcMAP 10.0 service pack 5 build 4400