Time difference

J

John

Hi

I am trying to calculate time difference in hours between two times. I am
using the datediff function. The results are peculiar;

? DateDiff("h", "00:30", "6:30") returns 6, but

? DateDiff("h", "23:30", "6:30") returns -17!! which I have thought should
be 7.

How can I get the correct results?

Thanks

Regards
 
R

Rick Brandt

John said:
Hi

I am trying to calculate time difference in hours between two times.
I am using the datediff function. The results are peculiar;

? DateDiff("h", "00:30", "6:30") returns 6, but

? DateDiff("h", "23:30", "6:30") returns -17!! which I have thought
should be 7.

How can I get the correct results?

Thanks

Regards
Click to expand...

DateTimes in Access always have both a date and a time. When you don't specify
a time midnight is assumed and when you don't specify a date 12/30/1899 is
assumed. This explains your result because you were actually doing...

DateDiff("h", #12/30/1899 23:30#, #12/30/1899 6:30#)

....and with the earlier value first you get a negative.

Try adding 24 hours whenever the result is negative.
 
V

Van T. Dinh

If your required time difference is less than 24 hours, use:

?(DateDiff("h", "23:00", "06:00") + 24) MOD 24
7

HTH
Van T. Dinh
MVP (Access)
 
Top