Get Date of the last Friday in month

[AD_Taylor]

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!

 

[barry houdini]

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)

 

[AD_Taylor]

Worked perfectly, thanks very much for this!

 

[gisquared]

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.