Exclude a public holiday in an IF statement

[mf1]

Hello,

I need some help to write a formula to exclude a public holiday and I have attached a sample file.


Current Situation

· I have an 8 week course which is conducted on the same weekday, once per week for 8 weeks.

· Week 1 (session A) is selected via a drop down box

· Weeks 2 – 8 is populated using the following formula;

=IF($C12="","",$C12+7)


Requirement

I need a formula added to the IF statement to exclude a public holiday and select the next available weekday from the following week (or the week after if a public holiday falls on consecutive weekdays)


I have a list with all public holidays listed and this can be used for a lookup.


Note: Week 1 won’t require any formula as the course won’t start on a public holiday.


Thanks, mf1

 

[Haseeb A]

Hello Michael,

I am not sure is this you are looking. Try this one in C5, then copy to C11

=IF(C4="","",C4+7+(MATCH(1,ISNA(MATCH(C4+{7,8,9,10,11,12,13},INDEX(Table1[Date2]*(Table1[Public Holidays]="Y"),0),0))+0,0)-1))

 

[mf1]

Thanks for replying Haseeb.

I have applied your formula and it has excluded a holiday on Tuesday 4th November (which is good), however has applied the next weekday (Wednesday). All courses are on the same day every week, unless a public holiday, which means we skip a public holiday week and recommence the week after.

The answer I need in this example is Tuesday 11th November (assuming this is not a public holiday).

Regards,

mf1

I have attached the updated file with your formula.

 

[NARAYANK991]

Hi ,

Haseeb's formula will work , if you add a second criterion that the next weekday should also fall on the same day of the week as the starting date.

See the attached file.

Narayan

 

[mf1]

Hi Narayan,

Works perfectly, thank you very much.

Cheers,
mf1