Exlcuding Weekends and Holidays with DateDiff

[CSADataCruncher]

I'm currently calculating the interval between two date fields in our database. However, now I've been asked to exclude holidays and weekends.

Here is my original formula for just the plain date calculation:
DateDiff ("d",{@SRCreated},Minimum ({v_TimesheetDetailReview_CSA_Dept.StartDate}, {v_Incident.IncidentNumber}))

Can anyone tell me how to modify this formula so that it excludes holidays and weekends?

Thank you,
CSADataCruncher

 

[AndyMc]

From Ken Hamady:

http://www.kenhamady.com/form01.shtml

 

[CSADataCruncher]

Thank you, Ken,

I tried both formulas (here is what the main formula looks like:

WhileReadingRecords;
Local DateVar Start := {@SRCreated};
Local DateVar End := Minimum ({v_TimesheetDetailReview_CSA_Dept.StartDate}, {v_Incident.IncidentNumber})
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

But when I try to save and close the formula editor, I get a message saying there is an error... it says this portion of the text does not appear to be part of the formula:

Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

 

[CSADataCruncher]

Sorry... ignore that last part... I realized my error on the above formula. But now here is my problem. You may have noticed that I'm comparing an actual start date (and I have added the Date() function to the formula) to the "minimum" of an end date. What is happening is I have several times that a tech went onsite and I only need to know the difference between the date the call was requested and the first time the tech went onsite. So, now my error message is saying that the "minimum" function cannot be used because it must be evaluated later.

Any ideas how I can accomplish this task?

Thank you, again,
Peggy Neubert
CSADataCruncher

 

[CSADataCruncher]

WooHoo.

Thank you, Ken, I figured it out. I plugged your formula into the detail line and then took the minimum from there rather than trying to have it do all the calculations including the minimum (if that makes sense to anyone but me). Anyway, it's working so now I'll just add to the holiday list/formula. Thank you, again.

Peggy
CSADateCruncher