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)

1/1/2019

5/2/2019

7/3/2019

3/4/2019

19/4/2019

1/5/2019

19/5/2019

30/5/2019

1/6/2019

5/6/2019

6/6/2019

11/8/2019

1/6/2019

<colgroup><col></colgroup><tbody>
</tbody>

Working saturdays :

03.06.2019

04.06.2019

31.12.2019

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.