Calculating Time based on an 8 hour basis

A

Alaska1

I am using the DateDiff function to calculate time between two dates.

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

I am getting time based on 24 hour period. I need to calculate on 8 an hour
basis.
 
V

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top