phone: (540)338-0194

﻿ ## Crystal Reports formula examples

To learn the techniques used in these formulas get:

## The number of "Work Days" between two dates:

Calculating the number of calendar days between two dates is fairly simple.  However, if you need to calculate the number of work days, it gets a bit more difficult.  Not only do you have to account for weekends, in many instances you also have to account for holidays, which change each year.

If you want to include holidays you put your holiday list in the second formula and then place this formula in the report header.  Then suppress the formula or the section.

If you need to do business hours, see Formula #13.
To add a number of business days to a date use Formula #17.

NOTE - this formula counts both the starting date AND the ending date, if both are work days.   So if you start on Monday and end on Tuesday, this counts as 2 days.

//Main formula
Local DateVar Start := {StartDate};   // place your Starting Date here
Local DateVar End := {EndDate};
// 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

//Holiday Listing formula to go into the report header of the report.