Last Friday / First Monday of Month Formula Anyone Know?

[SamBo1234]

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

 

[Oaktree]

See :

http://www.mvps.org/dmcritchie/excel/datetime.htm#firstdate

and

http://www.mrexcel.com/board2/viewtopic.php?t=220797

 

[Todd Bardoni]

Book1
	A	B	C	D
1		LastFriday	FirstMonday
2	1/1/2006	1/27/2006	1/2/2006
3	2/1/2006	2/24/2006	2/6/2006
4	3/1/2006	3/31/2006	3/6/2006
5	4/1/2006	4/28/2006	4/3/2006
6	5/1/2006	5/26/2006	5/1/2006
7	6/1/2006	6/30/2006	6/5/2006
8	7/1/2006	7/28/2006	7/3/2006
9	8/1/2006	8/25/2006	8/7/2006
10	9/1/2006	9/29/2006	9/4/2006
11	10/1/2006	10/27/2006	10/2/2006
12	11/1/2006	11/24/2006	11/6/2006
13	12/1/2006	12/29/2006	12/4/2006
Sheet1

In B2:-

=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:-

=DATE(YEAR(A2),MONTH(A2),1)+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),1,0,6,5,4,3,2)

 

[SamBo1234]

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 ?

 

[Norie]

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

 

[SamBo1234]

Excellent Cheers all!.

 

[Todd Bardoni]

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
	A	B	C	D
1	Today	LastFriday	FirstMonday
2	8/30/2006	August25,2006	August7,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.