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
a month or months
to
a date:
Versions
5 - 7: (or see
below
for a link to download the V8 Date functions DLL file)
One of the most
complicated
tasks
in doing date math is to calculate a date that is a fixed number of
months
forward or backward. This is because the length of the months
varies
and you may cross years. You also have to decide how to
handle
month
endings (i.e. when you add one month to January
31). The
formula
below assumes that if your calculation puts you past the end of the new
month,
that you want to use the end of that month. One month from
January 31
becomes February 28 or February 29 (in leap years). This
feature
is
now automated in Version 8 (see below).
In this
example you
substitute
your source date for the field {Orders.Order Date} in the second
line.
You fill in the number of months with a literal number in the 3rd line,
or
you can use a parameter prompt field. The theory of the
report is
that
it takes the month you are in, and picks the 15th or the middle of that
month.
It then adds to that date 30.44 days for each month that you want to
move
forward. This will land in the appropriate month and year,
again
roughly
in the middle. It then adjusts to the same day of
the month
as
the source date. Finally it checks to see if that day of the
month is
available in that month. If that day isn't
available,
because
the month is too short, then the formula uses the last day of
the
month.
WhileReadingRecords;
DateVar
Xfield:={Orders.Order
Date}; //use your date field here
NumberVar
Xmonths:=3;
//use a negative number if you want to go back 3 months
DateVar
ND3:=Xfield-Day(Xfield)+Truncate(Xmonths*30.4375+15);
DateVar
ND4:=ND3+30;
DateVar
Begin3:=
Date(Year(ND3),Month(ND3),1);
DateVar
Begin4:=
Date(Year(ND4),Month(ND4),1);
;
If
Begin4 > Begin3 +
Day(Xfield)
- 1
then
Begin3+Day(Xfield)-1
else
Begin4 -1 ;
Versions
8 and later:
Version 8
introduced the DateAdd() function.
Users of
V5 -7
can dowload the new V8 dll from Seagate's web site at:
http://support.crystaldecisions.com/communityCS/FilesAndUpdates/UFLDateAdd.exe.asp?
Using
this function,
adding one
month would be simply:
DateAdd(
'm', 1 ,
{Orders.Order
Date} )