I am using Crystal Reports XI to create a report where I am trying to calculate the time (in hours) expired between the start and the finish date/time as listed in a SQL database. I only want to calculate the business hours (even if the event should span across two calendar days).
There are a couple of considerations. Our business hours are 7:00 a.m. to 7:00 pm, Monday through Friday. The event start time may occur after the business day ends but the clock on the event would need to be adjusted to the start at the beginning of the next business day. The event may even begin on a Saturday or Sunday, but the clock on the event should not start until the beginning of the first business day of the week (Monday).
Calculating the real hours between the event start and completion is easy: DateDiff("h",{startdatetime},{enddatetime}).
Given the constraints above, the business time difference between an event that occured at 08/01/08 6:00 pm and ended at 08/04/08 6:00 pm would be eight hours. (However, the DateDiff expression above would yield 72 hours.)
Any assistance you can provide would be greatly appreciated.