'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix NETWORKDAYS.INTL Returning Wrong Workdays in Excel
Question details
The user is experiencing incorrect workday calculations when using the NETWORKDAYS.INTL function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the total number of working days between a start date and an end date while excluding specific weekends and holidays.
- Observed behavior
- The function returns a higher number of workdays than expected because it natively includes both the start and end dates. Additionally, hard-coded holiday dates occasionally produce unexpected calculation results.
Ensure that your start date, end date, and holiday cells are formatted as valid 'Date' values rather than 'Text' to prevent basic formula calculation errors.
Adjust the Formula to Exclude Start and End Dates
By default, NETWORKDAYS.INTL includes both the start date and the end date in its workday calculation. You must manually subtract days to exclude them from the total.
The NETWORKDAYS.INTL function calculates the number of working days that fall on or between the start and end dates. If you are calculating a deadline offset or simply measuring the gap between two milestones without counting the anchor dates, the default inclusion will result in a count that is too high.
Click on the cell containing your current NETWORKDAYS.INTL formula.
Click into the formula bar at the top of the window to edit the function.
To exclude both the start date and the end date, append -2 to the end of your formula. For example: =NETWORKDAYS.INTL(A1,B1,11)-2.
Press Enter to update the calculation and verify that the intended workday count is now correct.
Reference Holiday Dates from Worksheet Cells
Hard-coding holiday dates directly into the formula can cause Excel to misinterpret the date format, leading to incorrect workday counts.
Easily Calculate Workdays with WPS Office
WPS Spreadsheet provides flawless support for the NETWORKDAYS.INTL function, making date calculations straightforward. It accurately parses holiday cell references and date parameters, ensuring your project timelines are always correct.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook to the sheet where you want to calculate the workdays.
- 2. Start the function: Select the target cell, go to the Formulas tab and click 'Insert Function', or simply type =NETWORKDAYS.INTL( to begin.
- 3. Select start and end dates: Click the cell containing your start date, type a comma, and click the cell containing your end date.
- 4. Define weekends and holidays: Input your weekend parameter (e.g., 1 for Saturday and Sunday), type a comma, and highlight the cell range containing your holidays.
- 5. Calculate the result: Add your closing parenthesis and press Enter. If you need to exclude the start and end dates, simply type -2 at the end of the formula before pressing Enter.
Frequently Asked Questions
Why does NETWORKDAYS.INTL count one more day than simple subtraction?
Simple date subtraction (End Date - Start Date) calculates the difference in days, effectively excluding the start date. The NETWORKDAYS.INTL function counts every valid working day involved in the period, meaning both the start and end dates are included by default.
Can I use the DATE function to hard-code holidays in NETWORKDAYS.INTL?
Yes, you can use the DATE function (e.g., DATE(2023,4,8)) inside the formula to ensure the date is recognized correctly by the spreadsheet. However, referencing a cell range containing your holidays is highly recommended because it is easier to update and less prone to unexpected syntax errors.
What string format should I use for custom weekends?
You can use a 7-character text string of 1s and 0s enclosed in quotation marks, where 1 represents a non-working day and 0 represents a workday. The string always begins on Monday. For example, "0000110" sets Friday and Saturday as the weekend.
