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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,095,978
Messages
5,447,679
Members
405,462
Latest member
est1989

This Week's Hot Topics

Top