excelexcel-formulawps

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

enter image description here

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?

Solution

  • 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:

    enter image description here

    • 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:

    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:

    enter image description here