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

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.

### 4 Responses to “How to get next Monday’s date in Crystal Reports”

1. Thanks for the Tip Victoria.

Like

2. Awesome Victoria. I might just use that in a report I’m developing!! 🙂

Like