Calculate Business Days with Start and End Times in Excel
Question details
Calculate the number of business days between two date-times using a 7:00 AM to 11:00 PM business schedule.

- 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.
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.
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.
In Excel, enter the start date and time in A2 and the end date and time in B2.
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))))
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.
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 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. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
- 2. Enter date-times: Enter the start date-time in A2 and the end date-time in B2.
- 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. Format and save: Choose General from the Home tab's number format options, then use File > Save As to save the workbook.

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.




