phone: (540)338-0194
email: ken@kenhamady.com

´╗┐

 Crystal Reports formula examples

To learn the techniques used in these formulas get:

The Expert's Guide to Crystal Reports Formulas
and
 Expert Techniques for Crystal Reports volumes 1 - 4

Adding a month or months to a date:

Versions 8 and later:

All versions of Crystal since version 8.0 (Released in 2000) include the DateAdd() function.  Even users with older versions like V5 -7 may have dowloaded the v8 function dll from. If you have the DateAdd functions, adding one month would be simply:

DateAdd( 'm', 1 , {Orders.Order Date} )

Versions 5 - 7: (for those that do NOT have the DateAdd function)

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

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 ;