phone: (540)338-0194
email: ken@kenhamady.com

´╗┐

 Crystal Reports formula examples

Adding/Subtracting a Number of Business Days:

These formulas are designed to add/subtract a specified number of business days from a specified starting date.  In either formula you put your date field on the second line, and the number of days to add/subtract on the third line.  If you want to include a list of holidays in your calculation, then you will need to add the bottom formula to your report header with your list of holidays.  The Holiday list should include the holidays for each year, and can include multiple years of holidays at one time.  It should not include holidays that fall on a weekend. 

If you need to find the number of business days between two dates, you can use Formula #1 .  

//Adding Business Days:
WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={@StartDate}; // Put your field name in here
NumberVar Add:= 2; // put the number of days here to add (a positive number)
NumberVar Added := 0;

WHILE Added < Add
Do (target := target +1;
    if dayofweek (target) in 2 to 6 and not (target in holidays)
        then Added:=Added+1
        else Added:=Added);
Target


//Subtracting Business Days:
WhileReadingRecords;
DateVar Array Holidays;
DateVar Target:={@StartDate};  //Put your field name in here
NumberVar Add:= -3; // Put in the number of days to subtract (a negative number)
NumberVar Added := 0;

WHILE Added > Add
Do (target := target -1;
    if dayofweek (target) in 2 to 6 and not (target in holidays)
        then Added:=Added-1
        else Added:=Added);
Target



//Holiday Array Formula for the report Header:
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),   // you can put in as many lines for holidays as you want.  
Date (2003,12,31)
];
0