How to Calculate Work Days Between Dates in Crystal Reports

This procedure outlines how to calculate ‘Work Days’ in between 2 dates using Crystal Reports.

Procedure:

1. Identify the 2 date fields you require to calculate between.
2. Copy the formula below into a new formula & name it appropriately.
3. Replace the Date 1 & Date 2 fields with the 2 fields already identified.
4. Test the formula to see if it returns the correct result.

Crystal Formula:

Local DateVar Start := {@Date 1}; // place your Starting Date here
Local DateVar End := {@Date 2}; // place your Ending Date here
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[i] ) in 2 to 6 and
Holidays[i] in start to end then Hol:=Hol+1 );
Weeks + Days – Hol