Calculating number of hours between 2 date/time values

[Keith G Hicks]

I have a customer who wants me to create a report that involves calculating
the number of hours (in decimal format) between the start date/time of a
work order and the end date/time of a work order. That's easy of course.

The hard part is that I need to subtract the time out for any holidays or
weekends that land in the period in question. There's a table of holiday
dates in the database that they maintain. All holidays for the purposes of
this project begin at 7AM the day of the holiday and end at 6:59AM the day
after the holiday. Weekends begin at 7AM Saturday and end at 6:59AM Monday.

It is possible for the start and end points of a work order to be in the
middle of a holiday or weekend.

Example 1: If the start date/time is noon on Friday and the end date/time
is 2PM on Tuesday, the total time is 50 hours.

Example 2: If the start date/time is 9AM on Saturday and the end date/time
is the following Thursday at 5AM then the total time is figured from 7AM
Monday to 5AM Thursday making a total of 70 hours.

Short of moving minute by minute through the time period and determining if
I'm in a holiday or weekend, I'm not sure how to even begin this. Does
anyone have any suggestions or has anyone done anything like this before?

Thanks in advance,

Keith

 

[Graham Mandeno]

Hi Keith

There are plenty of examples available for calculating the number of working
days between two dates - here is one on the Access Web:
http://www.mvps.org/access/datetime/date0012.htm

You can use a modified version of this, calculating hours instead of days.
The tricky part is dealing with the 7AM start of the day. I suggest you
subtract 7 hours from both the start and end times before you do the
calculation. Then you can simply subtract 24 hours for each
Saturday/Sunday/holiday that falls in the range.