Time Difference (1 Viewer)

[GreenshootProgrammer]

How do I create an expression field that calculates the difference between a start time and finish time in hours, e.g. display 1 as the difference between 10:00AM and 11:00AM. Thanks.

 

[Galaxiom]

Use the DateDiff function.

DateDiff("h", StartTime, EndTime)

 

[MarkK]

But to demystify it a little, date/time values are stored as numbers and in that number scheme, one day is 1.0. So if you want to find the difference between two times and express that in hours, you can do that math like this . . .

(#11:00# - #10:00#) * 24

In this case we subtract one date/time value from another to find the difference, but that date/time result is expressed in days. To express it in hours we multiply by 24 because 1 day = 24 hours.

Sometimes I find the functions like DateDiff() and DateAdd() can make dates daunting like they are some special case animal handled by complicated functions. They're not, just simple numbers where one day = 1. One hour = 1/24. One minute = 1/24/60. Etc...

And you can do math on them directly.

 

[Galaxiom]

Sometimes I find the functions like DateDiff() and DateAdd() can make dates daunting like they are some special case animal handled by complicated functions.

And you can do math on them directly.

Be careful.

Sometimes using simple maths on datetime will cause unexpected errors due to rounding discrepancies, especially in comparisons.

There are 1440 minutes in a day and consequently there are many integer minute times that cannot be precisely represented as a real number. Hours don't divide well either and seconds are an even bigger problem.

In many cases this may not matter but records can be completely missed under some querying structures where ordinary arithmetic is used instead of the functions.

DateDiff is designed to accommodate this quirk.

Here is the worst example I have encountered where using an arithmetic comparison failed completely while DateDiff worked. Even Between failed.

Admittedly it also involved ODBC but it does serve to demonstrate the extent of unexpected potential problems when casually handling dates as numbers.

 

[MarkK]

Agreed. Be careful. And if you will do comparisons, or if you will store the result of your date math, use date functions.

DateAdd(), DateDiff(), DatePart(), DateSerial(), DateValue(), TimeSerial(), TimeValue() and more, provided by VBA.DateTime. Check them out in the object browser.

But also, don't let date/times scare you, and realize they are just numbers that measure time in the same way that numbers can measure dollars or feet or inches and so on.