How do I calculate the date of last month's first Monday ?

[pumin]

Hi.

How do I calculate the date of last month's first Monday ?

Eg. If today is in the month of July 2006, the result will be June 5, 2006.

Thanks in advance.

Pumin

[John Mark Osborne]

I modified a formula from the KnowledgeBase on the filemaker.com web site to do what you want:

Let(

[Today = Get(CurrentDate);

FirstDay = Date(Month(Today) - 1; 1; Year(Today))];

GetAsDate(7 * Int((FirstDay + 7) / 7) +

Case(DayOfWeek(FirstDay) < 3; -6; 1))

)

You can place a date field reference in the first parameter of the Let function instead of the Get(CurrentDate) function.

[Raybaudi]

...or, if you want to easily remember:

Let(

first = Date ( Month ( Get ( CurrentDate ) ) - 1 ; 1 ; Year ( Get ( CurrentDate ) ) );

Case(

DayOfWeek ( first ) = 1;first + 1;

DayOfWeek ( first ) = 2;first;

DayOfWeek ( first ) = 3;first + 6;

DayOfWeek ( first ) = 4;first + 5;

DayOfWeek ( first ) = 5;first + 4;

DayOfWeek ( first ) = 6;first + 3;

DayOfWeek ( first ) = 7;first + 2;

)

)

[pumin]

Thanks a lot for fast replies. I learnt a new function today.

[LaRetta]

Let ( lastMonth = date ( Month ( date ) - 1 ; 1 ; Year ( date ) ) ; lastMonth + Mod ( 2 - DayOfWeek ( lastMonth ) ; 7 ) )

:wink2:

Edited July 6, 2006 by Guest
Corrected calc

[comment]

Hm... that looks slightly familiar. :joust:

[LaRetta]

I learn from the best! :laugh2: