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:
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