How to get the last monday of the month?

[yashsrawat]

Hello

How will u get the last monday date of the month, if the month first day is provided to you?

For example 1st aug 07 is given to you, how will u get that 27th aug 07 is last monday of august month?

Yash

 

[Richard Schollar]

Hi Yash

Assuming you have the Analysis Toolpak installed, you can use:

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),3)

 

[barry houdini]

Here's a way without Analysis ToolPak

=A1+32-DAY(A1+31)-WEEKDAY(A1+2-DAY(A1+31))

 

[yashsrawat]

Hey Thanks It works

Like to knwo what is the logic behind it. if u can share with me


Thanks a ton

 

[barry houdini]

Hello Yash

If A1 contains a date then

=A1-WEEKDAY(A1,3)

gives you the previous Monday (or if A1 is a Monday it gives you A1). This is because WEEKDAY(A1,3) returns a zero when A1 is a Monday, 1 if A1 is a Tueday, 2 if A1 is a Wednesday etc. up to 6 for Sunday. So, today is a Thursday

=TODAY()-WEEKDAY(TODAY(),3) gives me the previous Monday because WEEKDAY(TODAY(),3) = 3

For your query you can just substitute the last day of the month for A1, i.e.

EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),3)

As per Richard’s suggestion

My suggestion works on similar lines

=A1-WEEKDAY(A1-2)

will give you the Monday before A1 (but if A1 is a Monday it gives you A1-7). So for your query you need to substitute the first day of the following month for A1. You could do this again with Analysis ToolPak function EDATE, because A1 is always the 1st of a month

=EDATE(A1,1)-WEEKDAY(EDATE(A1,1)-2)

or you could get the 1st of the following month with the formula

=A1+31-DAY(A1+31)+1

so the formula for the last Monday becomes

=A1+31-DAY(A1+31)+1-WEEKDAY(A1+31-DAY(A1+31)+1-2)

which you can simplify to

=A1+32-DAY(A1+31)-WEEKDAY(A1+2-DAY(A1+31))