How to Calculate Business Hours Between Two Dates in Excel
Question details
Calculate the exact elapsed business hours between a start date and an end date while excluding non-working times like overnight periods, weekends, and specific public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project turnaround times, SLA compliance, or actual employee working hours based on start and end date-time stamps.
- Observed behavior
- Finding a way to calculate total working hours accurately without relying on VBA scripts, automatically omitting non-business hours.
Ensure that your start and end dates are formatted properly as Date and Time cells. Verify your company's standard daily working hours (e.g., 9:00 AM to 5:00 PM) before configuring the formula.
Use the NETWORKDAYS Function with Time Fractions
Calculate precise business hours by combining NETWORKDAYS to find total workdays with the MOD function to extract and adjust the time values.
This formula breaks down the calculation into three distinct parts: finding the remaining working hours on the first day, the elapsed working hours on the final day, and the total standard working hours for all full workdays in between.
The example formula assumes a standard workday of 8 hours, starting at 9:00 AM (represented as 9/24) and ending at 5:00 PM (represented as 17/24).
Place your 'Start Date and Time' in cell A2 and your 'End Date and Time' in cell B2.
Select the cell where you want the total business hours to appear and enter the following formula: =IF(B2="","",IF(NETWORKDAYS(A2,B2)=1,(B2-A2)*24,(17/24-MOD(A2,1))*24+(MOD(B2,1)-9/24)*24+(NETWORKDAYS(A2,B2)-2)*8))
Press Enter to calculate the result. The cell will display the total elapsed working hours as a decimal number. You can drag the fill handle down to apply this formula to multiple rows.
To exclude public holidays, create a list of holiday dates in a separate column (e.g., H2:H10) and add it as the third argument to the NETWORKDAYS function: NETWORKDAYS(A2, B2, H2:H10).
Calculate Business Hours Seamlessly in WPS Spreadsheet
WPS Spreadsheet natively supports the NETWORKDAYS and MOD functions, enabling you to calculate complex time metrics like elapsed business hours with ease. It functions as a lightweight, highly compatible alternative to Excel for all data analysis tasks.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the document containing your start and end date-time records.
- 2. Input the Formula: Select a blank cell and input the nested NETWORKDAYS and MOD formula to compute elapsed hours.
- 3. Adjust Time Constants: Modify the time fractions (e.g., 9/24 and 17/24) if your organizational working hours differ from standard 9-to-5.
- 4. Apply to Range: Press Enter to execute the calculation, then drag the fill handle to apply the business hours calculation across your entire data column.
Frequently Asked Questions
How do I exclude public holidays when calculating business hours?
List your holiday dates in a separate column range (e.g., H2:H10). Then, add this specific range to your NETWORKDAYS function as the third argument, like this: NETWORKDAYS(A2, B2, H2:H10). This ensures those days are ignored in the total business hours calculation.
Why does my formula return a negative number?
Negative results typically occur if the End Date is chronologically earlier than the Start Date, or if the timestamps fall outside your specified standard working hours. Ensure your start and end times are formatted as proper datetimes and occur within the expected 9:00 AM to 5:00 PM window.
Can I use NETWORKDAYS.INTL for non-standard weekend days?
Yes. If your business weekends fall on days other than Saturday and Sunday (for example, Friday and Saturday), replace NETWORKDAYS with NETWORKDAYS.INTL. This function allows you to specify a custom weekend parameter to define exactly which days of the week should be treated as non-working days.
