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.

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

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Next Monday In Crystal ReportsNext Monday In Crystal Reports - DynamicAccounting.net - April 25, 2016

    […] on July 30, 2009 by Mark Polino Fellow Dynamics GP MVP Victoria Yudin has a new post up covering getting next Monday’s date in Crystal Reports. I’m still dealing with this Monday so I’m not sure why she’s already looking […]

    Like

  2. Next Monday In Crystal Reports - DynamicAccounting.net - July 30, 2009

    […] Monday In Crystal Reports Fellow Dynamics GP MVP Victoria Yudin has a new post up covering getting next Monday's date in Crystal Reports. I'm still dealing with this Monday so I'm not sure why she's already looking ahead to […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: