Print Page | Close Window

First Monday of the month Formula?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15974
Printed Date: 02 May 2024 at 8:31am


Topic: First Monday of the month Formula?
Posted By: acertik
Subject: First Monday of the month Formula?
Date Posted: 24 Mar 2012 at 12:05pm
Does anyone know what formula I might be able to use to return a date for the first Monday of the month?
 
I am trying to come up with a way that a report will tell me specifically when Labor Day is every year (U.S.), which is the first Monday in September.
 
Thank you in advance,
 
Alexander Certik


Replies:
Posted By: rkrowland
Date Posted: 29 Mar 2012 at 2:44am 
dateadd("d",8-dayofweek(date(2011,08,31),crmonday),date(2011,08,31))
 
You could replace the 2011 with something like year({table.datefield}) to find what date labor day was in relation to a transaction date.
 
Regards,
Ryan.

Posted By: acertik
Date Posted: 29 Mar 2012 at 12:19pm
Thanks Ryan.
 
If you have time, would you be able to explain how this formula works? Only if it's convenient.
 
Thank you,
 
Alexander Certik

Posted By: rkrowland
Date Posted: 29 Mar 2012 at 11:10pm

The dateadd function is adding a calculated number (8-dayofweek(date(2011,08,31),crmonday)) of days ("d") to a specified date (date(2011,08,31)).

Our starting date is always the last day of August as the first Monday in September has to fall within 7 days of this date.
 
So basically we've got add x days to 31/08/YYYY.
 
x = 8-dayofweek(date(2011,08,31),crmonday)
 
This dayofweek part of the formula calculates what day of the week the last day in August was if we say a week begins on monday (crmonday). IE if the 31/08/YYYY is a monday it will return 1, it's was a tuesday it will return 2 etc.
 
We'll go with our example and say the 31st of August was a Tuesday.
 
This is what's returned; dateadd("d",8-2,31-08-YYYY)
 
Which basically means the first Monday was the 6th of September.
 
Regards,
Ryan.


Print Page | Close Window