I created a shapefile with a string field with dates in the 02/22/2012 format and was able to convert it using the "Convert Time Field" tool after a couple tries.
On my successful attempt I created a new field of type date and then ran the date conversion tool.
For the custom format I used "MM/dd/yyyy". I also specified the newly created date field as the output time field.
I checked the method with errors in the strings and even a string like "2/5/12" was converted to 2/5/2012.
(My initial failures came because I assumed the tool would create a new date field for me, that didn't happen during my test.)
I hope this helps.
In a shapefile, numeric zeros are stored as text "0", while empty cells will be stored as empty strings "". However, ArcGIS interprets an empty string as a 0.
Because you cannot do anything about this, you need to use a work-around. A standard method, which is quite resistant to corruption from the GIS (or any other software), is to put an extremely negative value in empty cells. The value should be so extreme that if you mistakenly try to map it or use it in a statistical summary, it will skew the results so badly something will obviously be wrong. Within ArcGIS itself, you can always cause such values to be ignored by means of a definition query or an explicit selection operation.
(In Excel, just select an entire column or group of columns, from the top data row to the bottom data row, and use Edit|Replace
to search for all empty cells and fill them with your NoData code. It may look ugly in the end, but this is a reliable way to indicate missing data. Even Excel sometimes treats empty cells as zeros: such unreliable behavior has caused many subtle errors and erroneous results.)
Typically, a NoData code for an attribute intended to be stored as a float is equal to or close to the most negative float the computer can handle: around -10^38 for single precision or -10^304 for double precision. For a decimal attribute, use the most negative possible value. For instance, if your lat,lon values need 10 digits (including sign and decimal point) and you want five decimal places of precision, the most negative value you can store is -999.99999 (which obviously is an invalid latitude or longitude). Using such conventions makes it easy to recognize and remember these codes solely from knowledge of the field type in the database.
Incidentally, even using zeros for missing (lat,lon) is not such a bad idea: those points will plot in the ocean west of Africa, which is rarely within anybody's region of interest! Thus, they clearly show up as problem data, which is consistent with the underlying philosophy that errors, when they occur, should be so obvious and prominent that they will be identified and corrected. The silent, subtle errors are the deadly ones...
Best Answer
The reason that you are not able to save
Time
related information in ashapefile
is that the Shapefile format, does not support Time as an attribute. They only supportDate
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 theFile GDB
, for local storage. Even with these formats, there are limitations on how wellTime
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 ArcGISEdit - 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, as1899-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 aString
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.