workday with exceptions

[Ting_92]

Hi all,

Thank you for spending your time to read the post.

I have some questions regarding the excel.

I am in the process of creating an excel workday calculator. The issue is
1. There are some working saturdays
2. There are some non-working holidays (which i grouped under public holidays)

I have found the excel formula to exclude public holiday, but i cannot find back the formula to include.

Here is the function i have currently :
=WORKDAY.INTL(WORKDAY(N2,-2,Sheet5!$E$1:$E$26),-4,11,Sheet5!$E$1:$E$26)
Reason for seperated double function is as some days are inclusive of saturday calc. and some doesnt.

I duno how to include the working saturdays in the formula
Can anyone help?

Ultimately, I hope someone can help me with a public function creation of an excel vba so that it can be easily used. The dates for public holiday and inclusion hopes to be included inside the VBA.

Holidays (PH + non working days)

[TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]1/1/2019[/TD]
[/TR]
[TR]
[TD="align: right"]5/2/2019[/TD]
[/TR]
[TR]
[TD="align: right"]7/3/2019[/TD]
[/TR]
[TR]
[TD="align: right"]3/4/2019[/TD]
[/TR]
[TR]
[TD="align: right"]19/4/2019[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2019[/TD]
[/TR]
[TR]
[TD="align: right"]19/5/2019[/TD]
[/TR]
[TR]
[TD="align: right"]30/5/2019[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2019[/TD]
[/TR]
[TR]
[TD="align: right"]5/6/2019[/TD]
[/TR]
[TR]
[TD="align: right"]6/6/2019[/TD]
[/TR]
[TR]
[TD="align: right"]11/8/2019[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2019
[/TD]
[/TR]
</tbody>[/TABLE]

Working saturdays :
[TABLE="width: 33"]
<colgroup><col></colgroup><tbody>[TR]
[TD]03.06.2019[/TD]
[/TR]
[TR]
[TD]04.06.2019[/TD]
[/TR]
[TR]
[TD]31.12.2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Have Saturdays as a normal working day then have the non working saturdays as holidays.

 

[Tetra201]

See id the following formula works for you:

=WORKDAY.INTL(N2,O2,"00000"&IF(COUNTIF(Exceptions,WORKDAY.INTL(N2,O2,"0000000",Holidays)),"00","11"),Holidays)

Here N2 is the cell with the start date;
O2 is the cell with the number of workdays to count;
Holidays is a named range with holiday dates;
Exceptions is a named range with working Saturdays and/or Sundays.