Calculation Issues

Calculate Business Days with Start and End Times in Excel

Emma BrownEmma Brown Nov 18, 2026 869 views

Question details

Calculate the number of business days between two date-times using a 7:00 AM to 11:00 PM business schedule.

Calculate Business Days with Start and End Times in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains start and end date-times, and the user wants to count qualifying weekdays.
Observed behavior
The desired result counts qualifying weekdays while considering the start and end times and excluding weekends.
Before you start

Enter valid Excel date-time values in A2 and B2, with the start no later than the end. This method treats Saturday and Sunday as weekends and counts qualifying days, not elapsed business hours.

Solution 1Recommended

Count qualifying business days with NETWORKDAYS

Use this formula to apply the 7:00 AM and 11:00 PM cutoffs without subtracting weekend boundary dates.

A weekday counts when the interval reaches 7:00 AM or later and starts before 11:00 PM on that day. The result is a whole-day count, not a calculation of partial days or working hours.

1
Enter the date-times

In Excel, enter the start date and time in A2 and the end date and time in B2.

2
Add the formula

Select an empty cell and enter =IF(OR(A2="",B2="",B2<A2),"",MAX(0,NETWORKDAYS(A2,B2)-NETWORKDAYS(A2,A2)*(MOD(A2,1)>=TIME(23,0,0))-NETWORKDAYS(B2,B2)*(MOD(B2,1)<TIME(7,0,0))))

3
Format the result

Select the formula cell and choose Home > Number Format > General. The formula returns a blank if either input is empty or the end precedes the start.

4
Exclude holidays if needed

Enter holiday dates in E2:E10, then add $E$2:$E$10 as the third argument to each of the three NETWORKDAYS calls in the formula.

Count qualifying business days with NETWORKDAYS
Cutoff times: An end time of exactly 7:00 AM counts that weekday; a start time of exactly 11:00 PM does not. Weekend or holiday boundary dates are not subtracted a second time.
Calculate in WPS Spreadsheet

Count business days with time cutoffs in WPS Spreadsheet

Use the same NETWORKDAYS formula in WPS Spreadsheet to count weekdays within the 7:00 AM to 11:00 PM schedule.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
  2. 2. Enter date-times: Enter the start date-time in A2 and the end date-time in B2.
  3. 3. Calculate the count: In an empty cell, enter =IF(OR(A2="",B2="",B2<A2),"",MAX(0,NETWORKDAYS(A2,B2)-NETWORKDAYS(A2,A2)*(MOD(A2,1)>=TIME(23,0,0))-NETWORKDAYS(B2,B2)*(MOD(B2,1)<TIME(7,0,0))))
  4. 4. Format and save: Choose General from the Home tab's number format options, then use File > Save As to save the workbook.
Apply business-day time cutoffs with a single formulaExclude weekends and optional holiday datesOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Does NETWORKDAYS include weekends?

NETWORKDAYS excludes Saturdays and Sundays. For different weekend days, consider NETWORKDAYS.INTL and specify the appropriate weekend setting.

How do I exclude holidays from the calculation?

List holiday dates in a range such as E2:E10 and add that range as the third argument to every NETWORKDAYS call in the formula.

Why is the result lower when the start is after 11:00 PM?

The start date is excluded because it falls outside the 7:00 AM to 11:00 PM schedule. The formula only subtracts it if it is otherwise a qualifying business day.

Can this formula calculate business hours?

No. It counts qualifying dates as whole days. Calculating elapsed business hours requires a different formula that adds the time within each day's working window.