First Monday of Month

[fallsr]

I needed the first Monday of every month for performance indicator work. I used a formula from the BusinessObjects Knowledge Base article number c2006781 as the basis for the following code.

Create a formula like @FirstMondayOfMonth

Local NumberVar yr;
Local NumberVar mo;

yr := ToNumber(Year(CurrentDate));
mo := ToNumber(Month(CurrentDate));


Local DateVar mon1 := CDate(yr, mo, 1);

Local DateVar firstMondayOfMonth := mon1 + (8 - DayOfWeek(mon1, crMonday))mod 7;

Local NumberVar monthNumberOfFirstMonday := DatePart("m", firstMondayOfMonth, crMonday);

DateAdd("m", mo - monthNumberOfFirstMonday, firstMondayOfMonth)

Thanks,
Bob

 

[mbarron]

Or

datevar day1:= dateadd("d",-day({date.field})+1,date({date.field}));
if dayofweek(day1)=2 then day1 else
if dayofweek(day1)=1 then day1 + 1 else
day1 - dayofweek(day1)+9

Mike

 

[goranm]

Or

DateVar Temp := CurrentDate - Day(CurrentDate)+6;
DateVar FirstMonday:= Temp - DayOfWeek(Temp)+2

Goran

 

[rhinok]

If you're using SQL Server, you can create a SQL Expression, such as:

(
SELECT
DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)
)

~Kurt