Greetings,

I hope everyone is doing well.

I have a table setup with multiple date/time columns that I need to run date comparisons on. Initially, I was just using a simple Date1 - Date2 calculation to get the difference in days. However, my requirements have recently changed such that I must calculate elapsed business hours rather than just the plain elapsed time.

For example, let's say Monday's and Tuesday's business hours are 7am - 7pm. If an order is placed at 4pm on Monday and delivered at 8am on Tuesday, the regular elapsed time would be 16 hours. The elapsed time in business hours would be 4 hours.

Therefore, I would need an algorithm that could calculate elapsed business hours if given a schedule of business hours.

Before I go off to try to put this together, I was wondering if anyone had tackled a similar task and had some sample code to offer.

Thanks for the help.

I don't have all the code syntax down, but an algorithm such as the following would do the trick for you:

Dim dteCOB As Date
Dim dteSOB As Date
Dim HoursPerDate = 12
Elapsed = 0
If DateValue(Date1) = DateValue(Date2) Then
  Elapsed = TimeValue(Date2) - TimeValue(Date1)
Else
' You may need to do some string manipulation on the DateValue returns below to adjust the # locations to the start & end of the date
  dteCOB = DateValue(Date1) & "7:00 PM"
  dteSOB = DateValue(Date2) & "7:00 AM"
  Elapsed = DateDiff('h', dteCOB, Date1) + DateDiff('h', Date2, dteSOB) + (DateDiff('d',Date2,Date1) - 1) * HoursPerDay
End If

Hope that helps.

Thanks for the reply. I haven't had a chance to try it yet because I had a new "hot" task thrown at me. I am sure nobody else ever has to deal with anything like that...

Thanks again for the help on this one. I sat down yesterday to start putting this together and the sample algorithm you gave helped a lot. I am really close to having it done now.