Calculating number of hours between 2 date/time values

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

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
 
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.
 
Back
Top