'%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 Calculate Average Working Days in Excel (Excluding Weekends)
Question details
The user needs to calculate the average time taken to complete tasks by counting only working days, effectively excluding weekends and custom holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking task completion times, project management metrics, or employee performance evaluation where only active business days should be included in the average.
- Observed behavior
- To obtain an accurate average of working days across multiple tasks, the user must correctly nest the NETWORKDAYS or NETWORKDAYS.INTL functions within an AVERAGE function, or use dynamic arrays like MAP and LAMBDA for complex ranges.
Ensure your start dates and end dates are formatted correctly as standard Excel dates. If you plan to exclude specific dates, compile your public or company holidays into a dedicated cell range on your worksheet before applying the formulas.
Calculate Average Working Days Excluding Only Weekends
Use the combination of the AVERAGE and NETWORKDAYS functions to find the mean number of business days between multiple sets of start and end dates.
The standard NETWORKDAYS function automatically calculates the difference between dates while skipping Saturdays and Sundays. By passing range arrays into it and wrapping it in AVERAGE, you can quickly find the median timeframe for multiple projects.
Click on a blank cell in your worksheet where you want the calculated average working days to appear.
Type the formula =AVERAGE(NETWORKDAYS(A2:A6, B2:B6)) into the formula bar. Replace A2:A6 with the range containing your start dates and B2:B6 with your end dates.
Press Enter to execute the formula. Excel will output the average number of working days across all the specified date pairs, excluding standard weekends.
Exclude Both Weekends and Specific Holidays
Incorporate a holiday list into the NETWORKDAYS formula to exclude public or custom company holidays from your average working days calculation.
Use NETWORKDAYS.INTL for Custom Weekends and Holidays
Use the MAP, LAMBDA, and NETWORKDAYS.INTL functions to handle non-standard weekend days (such as Friday and Saturday) along with custom holidays.
Use WPS Spreadsheet to Calculate Average Business Days
WPS Spreadsheet fully supports Excel's advanced date functions, including NETWORKDAYS, NETWORKDAYS.INTL, and dynamic arrays. You can seamlessly calculate project timelines and average working days for free with full compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your task dates.
- 2. Enter the start and end dates: Ensure your start dates and end dates are inputted into two matching, parallel columns.
- 3. Type the formula: Click an empty cell and type the formula =AVERAGE(NETWORKDAYS(A2:A6, B2:B6)), adjusting the cell references to match your data.
- 4. Get the result instantly: Press Enter to instantly generate the average number of working days excluding weekends.
Frequently Asked Questions
Why does my NETWORKDAYS formula return a #VALUE! error?
This error typically occurs if the dates in your start, end, or holiday ranges are entered as text strings rather than recognized Excel serial dates. Select your date cells, right-click, choose 'Format Cells', and ensure they are properly formatted as Dates.
Can I calculate the average working days across non-contiguous cells?
Yes, but you cannot pass non-contiguous ranges directly into the NETWORKDAYS array. You must calculate the working days for each specific date pair individually using NETWORKDAYS in a helper column, and then use the AVERAGE function on the results of that helper column.
How does the weekend string parameter work in NETWORKDAYS.INTL?
The custom weekend parameter is a 7-character text string representing the days from Monday to Sunday. A '0' indicates a standard workday, and a '1' indicates a non-workday (weekend). For example, to set Thursday and Friday as the weekend, you would use the string '0001100'.
