Get Date of the last Friday in month

Well-known Member
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!

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

Well-known Member
Worked perfectly, thanks very much for this!

gisquared

New Member
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.

Replies
5
Views
102
Replies
18
Views
583
Replies
4
Views
208
Replies
3
Views
22
Replies
1
Views
198

1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

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.

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