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