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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]

Month​

[/td][td]

Last Friday​

[/td][td]

​

[/td][td]

Holidays​

[/td][/tr]
[tr][td]

2​

[/td][td]

Jan 2015​

[/td][td]

Fri 01/23/2015​

[/td][td][/td][td]

Fri 01/30/2015​

[/td][/tr]
[tr][td]

3​

[/td][td]

Feb 2015​

[/td][td]

Fri 02/27/2015​

[/td][td][/td][td]

Fri 03/27/2015​

[/td][/tr]
[tr][td]

4​

[/td][td]

Mar 2015​

[/td][td]

Fri 03/20/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

Apr 2015​

[/td][td]

Fri 04/24/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

May 2015​

[/td][td]

Fri 05/29/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

Jun 2015​

[/td][td]

Fri 06/26/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

Jul 2015​

[/td][td]

Fri 07/31/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

Aug 2015​

[/td][td]

Fri 08/28/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

Sep 2015​

[/td][td]

Fri 09/25/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

Oct 2015​

[/td][td]

Fri 10/30/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

Nov 2015​

[/td][td]

Fri 11/27/2015​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

Dec 2015​

[/td][td]

Fri 12/25/2015​

[/td][td][/td][td][/td][/tr]
[/table]

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!