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!
 

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,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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top