Summary
To calculate the total number of work hours between two dates, you can use a formula based on the NETWORKDAYS function, where "start" is the start date, "end" is the end date, "holidays" is a range that includes dates, and "hours" is the number of work hours in a workday. In the example shown, the formula in D6 is:
=NETWORKDAYS(B6,C6,holidays)*8
where "holidays" is the named range G5:G7. If work hours vary by day of week, see Custom work schedule below.
Generic formula
=NETWORKDAYS(start,end,holidays)*hours
Explanation
In this example, the goal is to calculate the total number of work hours between the start dates in column B and the end dates in column C, based on an 8-hour workday. Weekends should not be counted, and in some rows, neither should the holidays listed in G5:G7.
The NETWORKDAYS function
The core of this formula is the NETWORKDAYS function, which is designed to calculate total working days between two dates, taking into account weekends and (optionally) holidays. NETWORKDAYS includes both the start and end date in the calculation, and excludes both Saturday and Sunday by default. The function will also exclude holidays when they are provided as the "holidays" argument, which must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours per day, 8 in the example shown.
In the example shown, the first two formulas use the NETWORKDAYS function:
D5=NETWORKDAYS(B5,C5)*8 // no holidays
D6=NETWORKDAYS(B6,C6,holidays)*8 // holidays provided
If your workweek includes days other than Monday through Friday, you can switch to the NETWORKDAYS.INTL function, which provides a "weekend" argument that can be used to define which days of the week are workdays and weekend days. NETWORKDAYS.INTL can be configured just like NETWORKDAYS, but it provides an additional argument called "weekend" to control which days in a week are considered workdays.
The next 4 formulas use the NETWORKDAYS.INTL function:
D7=NETWORKDAYS.INTL(B7,C7)*8 // Mon-Fri, no holidays
D8=NETWORKDAYS.INTL(B8,C8,11)*8 // Mon-Sat, no holidays
D9=NETWORKDAYS.INTL(B9,C9)*8 // M-F, no holidays
D10=NETWORKDAYS.INTL(B10,C10,1,holidays)*8 // M-F, w/ holidays
Click the function names above to learn more about configuration options.
Custom work schedule
The formulas above assume all working days have the same number of work hours. If work hours vary by day of week, you can use a formula based on the SEQUENCE, WEEKDAY, and MID functions instead. In the worksheet below, the work schedule is entered in column D as a text string of 7 digits, one for each day of the week from Sunday to Saturday, and the formula in E5, copied down, is:
=LET(
dates,SEQUENCE(C5-B5+1,,B5),
SUM(MID(D5,WEEKDAY(dates),1)*ISNA(XMATCH(dates,holidays)))
)
The result in E5 is 36 hours, based on a custom schedule where 8 hours are worked Mon-Fri, 4 hours are worked on Saturday, and Monday, September 7 is a holiday. The schedule can be changed as desired.
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday=7.
The trick to this formula is assembling an array of dates that you can feed into the WEEKDAY function. This is done with SEQUENCE, which needs the number of days and the start date:
SEQUENCE(C5-B5+1,,B5)
C5-B5+1 is 7, because the start and end dates are both included, so SEQUENCE returns 7 consecutive dates, beginning with the start date in B5:
{46272;46273;46274;46275;46276;46277;46278}
Each number in the array represents a date. The LET function assigns this array to the variable "dates", so it can be used twice. The WEEKDAY function then evaluates the array and returns an array of weekday values:
{2;3;4;5;6;7;1}
These numbers correspond to the day of week of each date. They are provided to the MID function as the start number argument, along with the value in D5, "0888884" for text:
MID("0888884",{2;3;4;5;6;7;1},1)
Because we are giving MID an array of start numbers, it returns an array of results like this:
{"8";"8";"8";"8";"8";"4";"0"}
These values correspond to the hours worked on each day from the start date to the end date. Note the values in this array are text, not numbers. To convert to actual numbers, we multiply by a second array created to manage holidays, as explained below. The math operation coerces the text to numeric values.
To handle holidays, we use ISNA, XMATCH, and the named range "holidays" like this:
ISNA(XMATCH(dates,holidays))
The XMATCH function returns a number when a date is found in holidays and the #N/A error when not. The ISNA function then returns TRUE for the #N/A errors, so TRUE represents a regular day and FALSE represents a holiday:
{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
Finally, the two arrays are multiplied by each other inside SUM. The math operation coerces TRUE and FALSE to 1 and zero, and the text values in the first array to numeric values, so in the end we have:
=SUM({8;8;8;8;8;4;0}*{0;1;1;1;1;1;1})
=SUM({0;8;8;8;8;4;0})
SUM then sums all items in the array and returns a result of 36.
Hours in cells
The schedule string holds one digit per day, so it can't express a 7.5-hour or a 10-hour day. In that case, you can enter the hours for each day in a separate cell. In the worksheet below, the hours for each day of the week are in G5:G11, from Sunday to Saturday: 7.5 hours Monday through Friday, and 4 hours on Saturday. The formula in D5, copied down, is:
=LET(
dates,SEQUENCE(C5-B5+1,,B5),
SUM(INDEX(hours,WEEKDAY(dates))*ISNA(XMATCH(dates,holidays)))
)
where "hours" (G5:G11) and "holidays" (B11:B13) are named ranges.
This formula works like the custom schedule formula above, but the INDEX function replaces MID. WEEKDAY returns a number from 1 to 7 for each date, and INDEX uses each number to return the hours in that position in "hours". For the week of September 7 in row 5, the result looks like this:
INDEX(hours,{2;3;4;5;6;7;1}) // returns {7.5;7.5;7.5;7.5;7.5;4;0}
Unlike MID, INDEX returns numbers, not text. The holiday test works as before, so the formula becomes:
=SUM({7.5;7.5;7.5;7.5;7.5;4;0}*{0;1;1;1;1;1;1})
and SUM returns 34 hours: 7.5 hours on 4 days plus 4 hours on Saturday, because Monday, September 7 is a holiday.
Custom schedule in older versions of Excel
In older versions of Excel without SEQUENCE and LET, you can create the array of dates with ROW and INDIRECT and use MATCH instead of XMATCH. This formula works in any version of Excel:
=SUMPRODUCT(MID(D5,WEEKDAY(ROW(INDIRECT(B5&":"&C5))),1)*ISNA(MATCH(ROW(INDIRECT(B5&":"&C5)),holidays,0)))
ROW interprets the concatenated dates as row numbers and returns the same array of dates as SEQUENCE. Without LET, the array is created twice, once for WEEKDAY and once for MATCH. SUMPRODUCT handles the arrays natively, so the formula doesn't need to be entered with Control + Shift + Enter. One difference: if the end date is earlier than the start date, SEQUENCE returns #VALUE!, but this formula quietly returns the hours as if the dates were in order.
