excelexcel-formulawps

WORKDAY.INTL formula not working as intended


enter image description here

So i have this table shown above. K3 represents the date (manually entered) and L1 represents the lead time.

The conditions the result has to follow is shown below

  1. Days that should be included are Monday, Tuesday, Wednesday, Thursday, Saturday and Sunday. (Leaving out Friday as non working day)
  2. The Holidays should not be included =holidays!A2:A9

the formula in L3 is

=WORKDAY.INTL(K4, L2, "0111101", holidays!A2:A9) 

The list of holidays are shown in the table below.

enter image description here

As you can see, the result in L3 is 2nd March instead of 29th February. As this date does not fall on the holiday list and is not a Friday. Could someone help me with this, it would be much appreciated.


Solution

  • WORKDAY.INTL() is working as intended.

    In WORKDAY.INTL() function 1 represents a non-workday and 0 represents a workday. That said, 29th Feb'24 is Thursday and you have made it non-working day, hence in your formula: only Monday and Saturday are workdays rest are non workdays. Read MSFT Documentations here.

    enter image description here


    What has been used:

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

    What needs to be used:

    enter image description here


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

    Or, instead of using "0000100" use 16

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

    In context of your OP, the formula will be:

    =WORKDAY.INTL(K4, L2, "0000100", holidays!A2:A9)
    

    The following grid may help to understand as well:

    enter image description here