Date Fill filtering out fridays and holidays, and including Saturday and Sunday

Question

I am trying to fill in the dates horizontally, Below are the criteria for the dates.

1. Not Including Holidays
2. Saturday and Sunday should be included
3. Friday is a holiday therefore should not be included

Below is the formula I entered, =IF(OR(WEEKDAY(S$3)=6,COUNTIF('Holidays '!$A$2:$A$9,S$3)),"",S$3)

When i drag them, it shows the same date. What is the mistake I am doing?

Answer 1

If I have understood correctly from the OP then the following formula should work using WORKDAY.INTL() function:

Here are the conditions which are followed:

• Holidays are not included. (Based on this Georgian Calendar)
• Saturday and Sundays are included.
• Fridays are holidays hence should not be included.

---

---

• Formula used in cell C3

=WORKDAY.INTL(B3,1,16,Holidays[[Date]:[Date]])

---

The above formula needs to fill right, 16 means Friday is weekend day. In the above screenshot shown for two months i.e. from February'24 to March'24 and this should work for the rest of the months or years.

From the above screenshot, you can see that the Fridays are excluded, while Saturdays and Sundays are included, as well as the Holidays for the month of Feb say for example:

• 2/14/2024 - Day is Wednesday but Holiday as well hence not included.
• 2/19/2024 - Day is Monday but Holiday as well hence not included.
• 2/24/2024 - Day is Saturday but Holiday as well hence not included.

Likewise for the month of March'24 for the example shown and will be working for the rest of the months in the year.

---

Holidays List for the Feb'24 and March'24 Months used as sample:

---

Answer 2

If all you need to know is "why is it always producing the same date?", check your IF clause:

IF(condition, on true, on false)

Your question has cell T3 highlighted ... and the 'on not-a-holiday' value is "take the value from the previous column and put it here." At least you need to change this value.

I'm a bit fuzzy on how your COUNTIF() does the holiday check. Assuming that test is correct, however, I think you want T3 to contain:

IF(OR(your existing code here), S$3+2, S$3+1)

Answer 3

I think your formula is actually correct.

Except that, you should write your formula in Row4, not in Row3. While in range S3:AM3, you should input all dates. e.g. you can try write below in cell S3:

=SEQUENCE(1,20,DATE(2024,2,1),1)

Then drag your own formula from S4 to AM4, like below: