3rd Friday of month Excluding Holidays

[talt0612]

Hi all- i'm stuck on a problem: I need to calculate the 3rd friday of a given month, but if that date is a holiday (based on an array of predefined holiday dates), then I need to return the previous good workday (i.e. if Friday & Thurs are both holidays, return Wed.).

I can calculate the Third friday using the following:

A1: random date
B1: =MONTH(A1)&"/"&YEAR(A1)
C1: =B1+MOD(6-WEEKDAY(B1),7)+14

What I don't know how to do is say "if C1 matches any of the dates in the defined range "HolidayCalendar" then workday(c1,-1,HolidayCalendar)"

I'm using excel 2007 and an answer in excel or VBA is fine.

Thanks in advance for all your help.

 

[BiocideJ]

Hi all- i'm stuck on a problem: I need to calculate the 3rd friday of a given month, but if that date is a holiday (based on an array of predefined holiday dates), then I need to return the previous good workday (i.e. if Friday & Thurs are both holidays, return Wed.).

I can calculate the Third friday using the following:

A1: random date
B1: =MONTH(A1)&"/"&YEAR(A1)
C1: =B1+MOD(6-WEEKDAY(B1),7)+14

What I don't know how to do is say "if C1 matches any of the dates in the defined range "HolidayCalendar" then workday(c1,-1,HolidayCalendar)"

I'm using excel 2007 and an answer in excel or VBA is fine.

Thanks in advance for all your help.

You just about wrote the solution.
=WORKDAY(C1+1,-1,HolidayCalendar)
where HolidayCalendar is the named range of holidays.
NOTE: You will need the analyst toolpack add-in enabled to use the WORKDAY function.

 

[talt0612]

Yep. Sometimes the solution is right in front of you. Thanks very much BiocideJ.

 

[BiocideJ]

Yep. Sometimes the solution is right in front of you. Thanks very much BiocideJ.

No problem.