calculate hours correctly in Access-query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have a roster that needs to deduct start time from finish time and start
break from finish break extracted from a table.
Example Start 9.00 Finish 18.00 Start break 13.00 Finish break at 13.30.
Cannot get past a result of 8.7 hours irrespective if table is in numbers or
time (short)
 
Try typing an expression such as this into a fresh column in the Field row
in query design:

Hours: (DateDiff("n", [Start], [Start break]) +
DateDiff("n", [Finish break], [Finish)) / 60
 
If you get 8.7, your Fields are numeric and not datetime and therefore,
Access is doing decimal calculations, not time calculations since:

18.0 - 9.0 = 9.0
13.3 - 13.0 = 0.3

and
9.0 - 0.3 = 8.7 which is the result you got.

From your handle, I assume you are from NSW, Australia and the normal
separator between hours and minutes are colon (:), not period (.). Unless
you use non-standard Regional Settings on your OS, I doubt that Access will
recognise the time correctly if you enter "18.0".

Suggest you re-check the Field data type in your Table Design.
 
Back
Top