Calculating time in Access based on an 8 hour day

[Alaska1]

I am trying to calculate time based on an 8 hour day in Access. I have used
the DateDiff, but it gives me hours back based on 24 hours. Any suggestion!

Expr1:
DateDiff("h",[ClericalMetric].[OrderDateclerk],[ClericalMetric].[ClerkCompleteDate])

Order Date Clerk
10/1/2009 10:00 AM

Clerk Complete Date
10/2/2009 10:00 AM

 

[Stefan Hoffmann]

hi,

I am trying to calculate time based on an 8 hour day in Access. I have used
the DateDiff, but it gives me hours back based on 24 hours. Any suggestion!

Use simple arithmetics:

Expr1:
DateDiff("h",
[ClericalMetric].[OrderDateclerk],
[ClericalMetric].[ClerkCompleteDate]
) / 8

mfG
--> stefan <--

 

[vanderghast]

If you mean to count the number of working hours, assuming you work from
9:00 to 17:00, so, basically, you have to subtract day0 + 17:00 to day1 +
09:00. The exact time is somehow irrelevant, as soon as there are 8
consecutive hours of work by day.


DateDiff("h", startingDateTime, endingDateTime) - 16*DateDiff("d",
startingDateTime, endingDateTime)


Since DateDiff("d" ,... ) returns the number of day boundaries, we have the
meaning of the magical constant 16 as in 16 hours to subtract, for each day
boundary. If the starting date and ending date are the same day, that
datediff returns 0, appropriately.

Possible problems are:
- no check for weekend. Weekend days are considered worked.
- no check for lunch hour. Working from 10AM to 11AM is one hour of work;
same from 15:00 to 16:00; but from 11:00 to 15:00, the result is 4 hours,
which may include a possible not worked hour for lunch.
- no check for times being made in the appropriate working hours. If someone
entre from 04:00 today up to 19:00, next day, the result is in error.
- no check for time saving hours changes (which occur by night, anyhow)


Vanderghast, Access MVP