WORKDAY.INTL formula not working as intended
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
- Days that should be included are Monday, Tuesday, Wednesday, Thursday, Saturday and Sunday. (Leaving out Friday as non working day)
- 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.
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.
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.
What has been used:
=WORKDAY.INTL(B3,1,"0111101",Holidays[[Date]:[Date]])
What needs to be used:
=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:
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- How to inner-join in Excel (eg. using VLOOKUP)
- Scientific notation when importing from Excel in .Net
- More efficient way to loop through lines and duplicate if a certain character is found
- How to get the referenced value of a formula cell in the correct data type with SlapKit.Excel?
- Excel List Parse Table
- Extract number from the end of text string in Excel
- Normal.dotm equivalent in Excel for referencing the same single VBA code
- What Run time error in an SQL Query for join means
- Create A Chart On Each Worksheet Using The Data From That Worksheet
- Equivalent of Excel's Weeknum function in Python
- Any way to set minimum Cell Height in an Excel Sheet?
- Excel Power Query syntax issue transform multiple columns of records
- Userform with Excel 2018 for MacOS
- How to Rank Items by Multiple Criteria in Excel
- Extracting digits from a cell with varying char length
- Find the minimum value of a range based on presence of a substring in another range
- Grouping dates from rows
- How do I stop my for loop from erroring out
- Excel: Extract a sequence of n numbers from a string with a formula
- How to copy multiple sheets to a master sheet using distinct column headers
- DLL load failed while importing aggregations: The specified module could not be found pyhton pandas excel files merging
- With openpyxl, how do I collapse all the existing groups in the resultant excel?
- tcom tcl: Error when trying to add hyperlink with link of length more than 255 characters
- Concat in EXCEL like MySQL
- How to copy the conditional formatting without copying the rules from a Conditional Formatted cell?
- VBA Speeding Up Index-Match for Large Dataset
- Date cleaning in excel,date values align to the left
- Selecting sheets based off of a dynamic range, then exporting
- Importing XML from SharePoint by looping through a set of variables