Diff. betw. 2 times excluding holidays, weekend, & After Business Hrs

[DarthGAD]

I need to calculate the time lapse between two DateTime Entries excluding holidays, weekends, and after business hours.

I am using Crystal Pro 10 with SQL, Access, and Excel.

I found a solution in the FAQ but it seems to have a problem. The solution is found in faq767-5430

The problem is that it does not calculate correctly.

Sample Data:
Inc. # In Date & Time Out Date
111111 11/30/04 02:00:00 AM 12/01/04 08:00:00 AM
222222 11/30/04 10:00:00 PM 12/01/04 08:00:00 AM
333333 11/30/04 05:00:00 PM 12/01/04 08:00:00 AM
444444 11/30/04 05:00:00 PM 12/01/04 07:00:00 PM

Business Hours are 7:00 am to 6:00 pm. Monday to Friday.

This is what I get with the formulas in faq767-5430

Inc. # Time Difference
111111 5 hrs
222222 -3 hrs
333333 2 hrs
444444 -11 hrs

Expected Results:
Inc. # Time Difference
111111 12 hrs
222222 1 hr
333333 2 hrs
444444 12 hrs

Explanation of Expected Results:
Inc. # Time Difference
111111 12 hrs
Came in before business hours on the same day so the clock starts at 7am on 11/30. From 7am to 6pm on 11/30 is 11 hrs and from 7am to 8am on 12/1/04 is one hour for a total of 12 hrs.

222222 1 hr
Came in after business hours so the clock starts the following business day at 7am. From 7am to 8am on 12/1/04 is one hour.

333333 2 hrs
Came in at 5pm on 11/30. From 5pm to 6pm on 11/30/04 is one hour and from 7am to 8am on 12/01/04 is one hour for a total of 2 hours.

444444 12 hrs
Came in at 5pm on 11/30. From 5pm to 6pm on 11/30/04 is one hour. Out at 7pm on 12/01/04 therefore clock ends at 7am on 12/02/04. From 7am to 6pm on 12/01/04 is 11 hours. From 7am to 7am on 12/02/04 is zero hours for a total of 12 hrs.

I have looked at the formula to see what i need to change to make it work but I can't see it. Any assistance is greatly appreciated.

GusDarino

 

[synapsevampire]

You might consider taking the big kid approach and set up a Periods table:

faq767-4532

I has the SQL to generate the Periods Table using SQL Server, which I assume is what you meant when you referenced the language SQL.

-k

 

[plextor]

Has anyone been able to identify a fix for the above formula? I returns incorrect values when an open/close date is outside the stated business hours.