'%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 Working Hours with NETWORKDAYS in Excel
Question details
The user needs a formula to calculate total working hours between a start date and an end date using the NETWORKDAYS function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the total number of elapsed working hours between two given dates, while automatically accounting for weekends and optionally excluding holidays.
- Observed behavior
- A formula is required to convert elapsed working days into total working hours, handling cases where the end date might be blank by defaulting to the current date.
Ensure your start and end dates are properly formatted as Date values in your spreadsheet to prevent the NETWORKDAYS function from returning calculation errors.
Using the NETWORKDAYS Formula to Calculate Working Hours
Multiply the result of the NETWORKDAYS function by 24 to convert elapsed working days into working hours.
The NETWORKDAYS function automatically calculates the number of working days between two dates, excluding weekends. By multiplying the total working days by 24, you can easily find the total working hours. You can also nest an IF function to handle missing end dates dynamically.
Click on the cell where you want the calculated total working hours to appear.
Type the formula =24*NETWORKDAYS(D6,IF(E6="",TODAY(),E6)) and press Enter. This assumes your start date is in cell D6 and your end date is in E6.
If you need to exclude specific company holidays, add a holiday range as the third argument: =24*NETWORKDAYS(D6,IF(E6="",TODAY(),E6), H2:H10), assuming cells H2 through H10 contain your holiday dates.
Calculate Working Hours Seamlessly Using WPS Spreadsheet
You can perfectly apply the NETWORKDAYS formula in WPS Spreadsheet to calculate elapsed working hours. WPS Office provides an intuitive data processing interface and works identically with standard spreadsheet functions.
- 1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open the document containing your start and end dates.
- 2. Select the target cell: Click on the specific cell where the calculated hours will be displayed.
- 3. Apply the NETWORKDAYS formula: Input the formula =24*NETWORKDAYS(Start_Date_Cell, End_Date_Cell) in the formula bar and press Enter to instantly see the total working hours.
Frequently Asked Questions
Does the NETWORKDAYS function include both the start and end dates in its calculation?
Yes, the NETWORKDAYS function is inclusive. It counts both the start date and the end date as working days as long as they do not fall on a weekend or a specified holiday.
How do I calculate working hours if my standard workday is 8 hours instead of 24?
To calculate hours based on an 8-hour workday, simply change the multiplier at the beginning of the formula from 24 to 8. Your adjusted formula will be =8*NETWORKDAYS(start_date, end_date).
Why is my NETWORKDAYS formula returning a #VALUE! error?
A #VALUE! error typically occurs if the start date or end date is not recognized as a valid date by the spreadsheet. Check your referenced cells and ensure they are formatted as Dates rather than text strings.
Can I customize the weekend days used in the formula?
Yes. If your weekends fall on days other than Saturday and Sunday, you should use the NETWORKDAYS.INTL function instead. It includes an additional argument that allows you to specify exactly which days of the week should be considered weekends.
