Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide On Location TrainingPublic ClassesIndividual TrainingIntroductory Course OutlineAdvanced Course OutlineFormula ExamplesNewsletter Back IssuesMy BlogResource LibraryConsulting ServicesSupport ServicesContact InformationLinks to third party productsBack to main pageMy Credentials
Free Crystal Reports formula examples from KenHamady.com


To learn the techniques used in these formulas get:


The Expert's Guide to Crystal Reports Formulas
and
 Expert Techniques for Crystal Reports I, II & III

Find it EZ

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.

The formula below does both.  It requires that you enter your list of holiday dates in a separate formula.  The list should not include holidays that will occur on weekends since they are already skipped by the formula.  However if you do include a weekend holiday the formula will ignore it.  The holiday list can include dates several years in advance.  There are even ways to have the formula generate a warning when the holiday list is about to expire (ask me about this).  To use this formula, simply copy it into your report and put your field names in on the second and third lines in place of the field names I have used.   If your fields are DateTime fields you should put your fields within the DATE() function to convert them to dates.  If you get an error you can try changing the first line to WhilePrintingRecords. 

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
WhileReadingRecords;
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.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0