Get Date of the last Friday in month

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
If there's one thing I really wish I understood more in Excel its how to use formulas to calculate days and times.

I'm looking for a formula that will give me the date of the last Friday in a month based on another date. I have the formula below at the moment that works quite nicely for March 2012 but not for any others. It's getting the 5 at the end to be more flexible that's got me stumped.

=DATE( YEAR(H12), MONTH(H12) + 1, 0) - (WEEKDAY( DATE( YEAR(H12), MONTH(H12) + 1, 0), 2) - 5 )

Any help is appreciated!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
This should work for any date in H12

=DATE(YEAR(H12),MONTH(H12)+1,1)-WEEKDAY(DATE(YEAR(H12),MONTH(H12)+1,1)-6)

The red 6 determines the day (1 = Sun through to 7 = Sat)

or if you don't mind using EOMONTH you can shorten using that

=EOMONTH(H12,0)+1-WEEKDAY(EOMONTH(H12,0)+1-6)
 

gisquared

New Member
Joined
Oct 22, 2013
Messages
6
This is great... I tweeked it just a touch to make it refer to the last Friday of the month of the previous cell. That way you can have a chart of all of the last Fridays for a year.

So.... B2 is my kick off for the chart, 02/24/17 ... my equation for C2 is "=EOMONTH(B2,1)+1-WEEKDAY(EOMONTH(B2,1)+1-6)" and the result is 03/31/17.

Drag that to the right and you will show April's Friday, May's Friday...etc. :D

Thanks for the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,291
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top