Crystal Reports Training by Ken Hamady, MS, Reporting
and Training Nationwide
Free
Crystal Reports formula examples from
KenHamady.com
To
learn
the techniques used in these formulas get:
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