Crystal Reports: Calculating Month Values in Crystal Reports

	Resources
	Best sellers: ● cView ● Report Analyzer ● cViewSERVER ● ReCrystallize
	Crystal Reports: Free trial
	Articles: ● Administration ● Advanced ● Basic ● Crystal eNL ● Database ● Financial ● Problems Solved Books: ● CR Books ● Database Books ● Developer Books
	Tools: ● All CR Tools ● CR Analyzers ● CR Bestsellers ● DataBase Tools ● CR Graphics ● International ● CR Mail UFLs ● ReCrystallizePro ● CR Schedulers ● CR UFLs ● CR Viewers
	Add'l: ● About us ● Contact Us ● cViewSUITE Ppt ● Support
	Crystal Reports on Steroids

Crystal Reports: Calculating Month Values

This article addresses how to calculate some month values.

Dates are among the more complicated data types to work with. People keep asking for interesting calculations on date fields.

Here's an example of one such request. The initial request was to calculate the last Friday of the month. We developed a series of useful formulas to get that, and several other handy date values. First formula calculates the last day of the month.

Local numbervar m:=month(CurrentDate) + 1;

Local numbervar y:=year(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);

Date(y,m,1)-1;

Once you have that formula, then it is quite easy to calculate the number of days in the month

Day({@LastDayofMonth)

From there, the Last Friday of the month is calculated as follows.

If DayofWeek({@lastDayofMonth}) >=6 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 6

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 1

If you require a different last day of the month, create a variation of the formula below. This example calculates the Last Wednesday of this Month

If DayofWeek({@lastDayofMonth}) >=4 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 4

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 3

Use these directly with your database fields or modify the formulas to make them a custom function (version 9 and above).

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.