How to get next Monday’s date in Crystal Reports

From the Flexible Solutions’ GP Reports July 2009 Newsletter, here is a great new tip: how to calculate next Monday’s date in Crystal Reports. This comes up periodically when someone wants to have weekly columns on a report showing totals of something like sales, expenses or invoices due.

So if you have a report parameter called ReportDate and you want to calculate the following Monday’s date, here is the formula:

if DayOfWeek({?ReportDate}) = 1
then dateadd("d",1,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 2
then dateadd("d",7,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 3
then dateadd("d",6,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 4
then dateadd("d",5,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 5
then dateadd("d",4,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 6
then dateadd("d",3,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 7
then dateadd("d",2,{?ReportDate})

Then to calculate the following Monday date, you can use the following formula:

dateadd("d",7,{@Week1date})

Just keep adding 7 for additional weeks.

As a corollary to this, let’s say you wanted the closest (as opposed to the following) Monday, here is the formula:

if DayOfWeek({?ReportDate}) = 1
then dateadd("d",1,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 2
then {?ReportDate} else
if DayOfWeek({?ReportDate}) = 3
then dateadd("d",-1,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 4
then dateadd("d",-2,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 5
then dateadd("d",-3,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 6
then dateadd("d",3,{?ReportDate}) else
if DayOfWeek({?ReportDate}) = 7
then dateadd("d",2,{?ReportDate})

The above will give you the following:

• on Monday – returns the ReportDate entered by user
• on Tuesday - returns the previous Monday’s date
• on Wednesday – returns the previous Monday’s date
• on Thursday – returns the previous Monday’s date
• on Friday – returns the following Monday’s date
• on Saturday – returns the following Monday’s date
• on Sunday – returns the following Monday’s date

For more Crystal Reports tips, check out the Working with Crystal Reports section on my GP Reports page. Past Flexible Solutions GP Reports newsletters can be found on the Flexible Solutions website.