# How to use DateDiff() Function across day transition in ArcGIS Field Calculator?

I am trying to calculate the time difference between two fields using the DateDiff() function in minutes. I have successfully calculated the times that fall within the same day but I am having trouble with the times that are occurring in day transition (after 11:59 PM). For example: My date fields are as such,

MM\dd\yyyy hh:mm:ss tt

So between these two times

01\17\2007 11:57:08 PM and 01\18\2007 12:15:01 AM

The field calculator would apply a huge negative number. I am thinking a conditional statement is needed for basic arithmetic but am at a loss.

Please be as detailed as possible. I am not a developer.

-
Would you be able to edit your Question to include the code which has been successful for "within the same day" and that which causes problems across days, please? Also, be sure to include the version of ArcGIS for Desktop, and the spatial data storage format (shapefile, file geodatabase, etc) that you are using. – PolyGeo Apr 18 '14 at 21:26
The formula I am using is:DateDiff("s", Field1, Fields) – randyjwolter Apr 19 '14 at 1:52
The formula I am using is: DateDiff("s", Field1, Fields). The problem with different days is mentioned above. The DateDiff() function doesn't "consider the date when it looks at the hh:mm:ss tt section of the field's row values. In using my example above, when DateDiff() calculates the number of seconds between 11:57:08 PM and 12:15:01 AM the calculation looks like this: 12:15:01 AM substract 11:57:08 PM. If you looked at it in military time 00:15:01 - 23:57:08 or 901 seconds subtract 86222 seconds or 901-86222 = -85321. The answer answer is -85321. I know how do do this in Excel, not ArcGIS. – randyjwolter Apr 19 '14 at 2:04
I am considering using this statement in the Field Calculator. What do you think? Code: Field1 = TimeValue() Field2 = TimeValue() IF Field1 < Field2 Then Output = DateDiff("s", Field1, Field2) Else Output = DateDiff("s", Field2, Field1) Expression: Output – randyjwolter Apr 19 '14 at 21:42
Would you be able to use the edit button beneath your Question to revise it with these additional details, please? That way you get access to the Code formatting button, and your Question continues to standalone rather than needing a train of comments to also be read. – PolyGeo Apr 19 '14 at 23:57

Update: Based on some testing I just did, I do not know why the function wasn't working for you. Per PolyGeo's answer there are issues related to both database type and field definition, but based on your question and comments you are working in a file geodatabase (folder.gdb), your field type is date, and your date/time are in a single field. You want the time difference in minutes. In that case you need to:

1. Add a numeric field (short, long, float, double) to your table. You cannot calculate in this manner to a date format field.
2. Right-click the new field heading in the table and select the Field Calculator.
3. Enter `DateDiff ("n", [date1], [date2])` in the Field Calculator and click Ok.

This should give you the numeric value in minutes between the two times. If you want seconds, per your later comment, replace the `n` with an `s`. I tested both successfully.

Now, if your date and time are in two different date fields, that may be causing the problem because the DateDiff function is assuming the times are in the same day. But I don't believe that to be the case, since the code below also worked for you - particularly the x number of days line.

I'm not an expert on the function or its ArcGIS implementation, but based on your description and some reading I see two issues with your method and proposed solution:

• If it is correct (and I no longer believe it is) that it ignores either the date or the time portion for any single calc, your comment proposed solution assumes there are no fields that differ more than 24 hours.
• As you've already found if the two times cross a day boundary you can no longer arrive at the difference by subtracting them in either order (hence the error in your comment math - the actual difference between the two times is 1073 seconds). DateDiff doesn't function this way. It treats the date as a value in a continuous sequence from an 'arbitrary' date, not a value in a sequence that resets each day.

If you want to treat times as a sequence that resets each day as in your comment, the math is different. Unless you know your data will never be more than 24 hours different, your calculation needs another step to compare days before then comparing the time. That step is also required to determine which method of seconds calculation (same day or adjacent days) is necessary. See the following untested code, which would go in the codeblock section (may need to check the Show Codeblock box); the lower box (TimeDiffField=) would just contain `y`.

``````dim x
dim y
dim das1
dim das2

das1 = (datepart ("h", [date1]) * 3600) + (datepart ("n", [date1]) * 60) + (datepart ("s", [date1]))
das2 = (datepart ("h", [date2]) * 3600) + (datepart ("n", [date2]) * 60) + (datepart ("s", [date2]))
x = datediff ("d", [date1], [date2])

if x < 1 then
y = datediff ("s", [date1], [date2])
else then
y = (86400 - das1) + das2 + ((x-1) * 86400)
end if
``````
-
These are 9-1-1 response times and will not be going a 24 hour time frame. – randyjwolter Apr 21 '14 at 0:51
@randyjwolter In that case you would be able to eliminate the third part of the else (where the seconds for full days between dates is added), but the rest of it stays the same - you still have to check if you are crossing a day boundary. I have revised my pseudo-code to something that should actually work, but I have not tested it. – Chris W Apr 21 '14 at 18:52
It worked thanks. – randyjwolter Apr 23 '14 at 22:10
@randyjwolter Happy to help, and I got to learn some things out of it myself. Please consider clicking the check to accept the answer. – Chris W Apr 25 '14 at 3:53
@randyjwolter It looks like ChrisW did quite a lot of work on this so I think you should reward that effort by clicking the Accept (green tick) button next to his Answer. – PolyGeo May 29 '14 at 2:14

It is possible that you may be encountering the same issue as in this ArcGIS Discussion Forum thread.

From there the advice would seem to be:

Is the data in a GeoDatabase? If the data is a shapefile then you probably won't get good or any results. If the data is in a PGDB then try testing queries in Access directly. When it works move them into ArcMap. If it's in SDE then test using SQL queries.

Are the columns in a date datatype? Also what is the target column data type?

The target needs to be integer or float if you are calculating minutes.

Check out the date functions at:

http://www.techonthenet.com/access/functions/

-
Yes the table is in a geodatabase. I don't know what a PGDB is and I don't have Access. Yes the columns are in a Date type column. The target column can be a any type. I will make it float type. I will check out that function page. I need some examples if at all possible. – randyjwolter Apr 18 '14 at 22:24
PGDB is personal geodatabase and is in the format of a Microsoft .mdb (Access database), as opposed to FGDB which is file geodatabase and in the ESRI custom format of a folder with .gdb extension containing other files. – Chris W Apr 20 '14 at 20:13
Its ESRI's geodatabase. – randyjwolter Apr 22 '14 at 15:20