Dealing with "Time" formats from EXCEL to ESRI

DanielAmrine — Tue, 10 Feb 2015 17:21:33 GMT

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

Thanks!

Re: Dealing with "Time" formats from EXCEL to ESRI

VinceAngelo — Fri, 27 Mar 2015 00:22:13 GMT

What data format are you using? dBase (.dbf) only supports day resolution, so that would

be appropriate for shapefile.

- V

Re: Dealing with "Time" formats from EXCEL to ESRI

DanielAmrine — Tue, 31 Mar 2015 19:07:22 GMT

So in the .dbf you can only show it up to days? What about a feature class in a geodatabse...I suppose it still a .dbf

Thank you for responding.

Dan

Re: Dealing with "Time" formats from EXCEL to ESRI

VinceAngelo — Tue, 31 Mar 2015 19:14:49 GMT

There are several different "geodatabase" formats (none of which are dBase-based), and all of them have a higher resolution (seconds) than dBase (days).

- V

topic Dealing with "Time" formats from EXCEL to ESRI in Data Management Questions

Dealing with "Time" formats from EXCEL to ESRI

Re: Dealing with "Time" formats from EXCEL to ESRI

Re: Dealing with "Time" formats from EXCEL to ESRI

Re: Dealing with "Time" formats from EXCEL to ESRI