# How to get the last monday of the month?

#### yashsrawat

##### Board Regular
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Yash

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

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

Here's a way without Analysis ToolPak

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

Hey Thanks It works

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

Thanks a ton

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))

Replies
6
Views
433
Replies
4
Views
304
Replies
6
Views
401
Replies
15
Views
356
Replies
1
Views
379

1,221,522
Messages
6,160,308
Members
451,637
Latest member
hvp2262

### 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.

### Which adblocker are you using?

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

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