How to get the last monday of the month?

yashsrawat

Board Regular
Joined
Aug 11, 2006
Messages
106
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Yash

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

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Here's a way without Analysis ToolPak

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

yashsrawat

Board Regular
Joined
Aug 11, 2006
Messages
106
Hey Thanks It works

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


Thanks a ton
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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))
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top