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} )