# Last Friday / First Monday of Month Formula Anyone Know?

#### SamBo1234

##### Board Regular
Hi All,

A little too tricky for me this one so wondered if you guys with your supreme excel skills help me out !

All i need are two formulas:

One which returns the date of the last Friday of the Month.

One which returns the date of the first Monday of the Month.

i currently have the formula: =DATE(YEAR(NOW()),MONTH(NOW())+1,0) which provides the date of last day of the month

Regards
Sam

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Book1
ABCD
1LastFridayFirstMonday
21/1/20061/27/20061/2/2006
32/1/20062/24/20062/6/2006
43/1/20063/31/20063/6/2006
54/1/20064/28/20064/3/2006
65/1/20065/26/20065/1/2006
76/1/20066/30/20066/5/2006
87/1/20067/28/20067/3/2006
98/1/20068/25/20068/7/2006
109/1/20069/29/20069/4/2006
1110/1/200610/27/200610/2/2006
1211/1/200611/24/200611/6/2006
1312/1/200612/29/200612/4/2006
Sheet1

In B2:-
Code:
``=DATE(YEAR(A2),MONTH(A2)+1,0)-CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0)),2,3,4,5,6,0,1)``

In C2:-
Code:
``=DATE(YEAR(A2),MONTH(A2),1)+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),1,0,6,5,4,3,2)``

Hi,

Thanks for the reply it does help,

However i need a formula that will reflect the Last Monday of the current Month using the NOW() function. As i will not have defined dates.

Is this possible ?

Just replace A2 in Todd's formulas with NOW() or TODAY().

Excellent Cheers all!.

If performance is at all an issue it would be better to put the TODAY function in a cell and have the formulas refer to that cell.
Book2
ABCD
1TodayLastFridayFirstMonday
28/30/2006August25,2006August7,2006
Sheet1

And for what it's worth, I like the formula posted by NBVC (link provided by Oaktree) on how to find the last Friday of the month...very slick indeed.

Replies
3
Views
150
Replies
0
Views
406
Replies
19
Views
621
Replies
4
Views
619
Replies
3
Views
715

1,202,962
Messages
6,052,805
Members
444,602
Latest member
Cookaa

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