Last friday of month excluding holidays

[Trax]

Hi,

I have a formula that gives me the last Friday of the month, but would like to ensure that holidays are considered in the formula.

I have a list of holidays in a table named 'Holidays'. I have tried to add this in to the formula, but have had no luck.

=EOMONTH($W$1,0)+1-WEEKDAY(EOMONTH($W$1,0)+1-6)

Any suggestions?

 

[sheetspread]

http://www.mrexcel.com/forum/excel-...-last-friday-before-last-full-week-month.html

 

[Trax]

Hi, Many thanks for this, but this gives me the last working day of the month. I am looking for the last Friday of the month with the holidays taken into account.

 

[Jonmo1]

The last working day of a month isn't necessarily a Friday.

Try this to get the last Friday.

=EOMONTH(A1,0)+(7-WEEKDAY(EOMONTH(A1,0)+1))-7

Now you said to consider holidays as well.
So what exactly should happen if the last friday is a holiday?
Should it return the previous Thursday, or go back a whole week to the previous Friday?

 

[sheetspread]

or go back a whole week to the previous Friday?

That

 

[shg]

Row\Col	A​	B​	C​	D​
1​	Month​	Last Friday​	​	Holidays​
2​	Jan 2015​	Fri 01/23/2015​		Fri 01/30/2015​
3​	Feb 2015​	Fri 02/27/2015​		Fri 03/27/2015​
4​	Mar 2015​	Fri 03/20/2015​
5​	Apr 2015​	Fri 04/24/2015​
6​	May 2015​	Fri 05/29/2015​
7​	Jun 2015​	Fri 06/26/2015​
8​	Jul 2015​	Fri 07/31/2015​
9​	Aug 2015​	Fri 08/28/2015​
10​	Sep 2015​	Fri 09/25/2015​
11​	Oct 2015​	Fri 10/30/2015​
12​	Nov 2015​	Fri 11/27/2015​
13​	Dec 2015​	Fri 12/25/2015​

In B2, assuming that there are not two consecutive Fridays off at the end of the month,

=DATE(YEAR(A2), MONTH(A2) + 1, 1) - WEEKDAY(DATE(YEAR(A2), MONTH(A2) + 1, 2))
- 7*COUNTIF(Holidays, DATE(YEAR(A2), MONTH(A2) + 1, 1) - WEEKDAY(DATE(YEAR(A2), MONTH(A2) + 1, 2)))

Adapted from Date Calculations

 

[Trax]

Thanks Jonmo1 - it should return the previous Thursday.

 

[Jonmo1]

To go back to previous Thursday

=WORKDAY(EOMONTH(A1,0)+(7-WEEKDAY(EOMONTH(A1,0)+1))-6,-1,Holidays)

 

[Trax]

Thank you Jonmo1!