Calculate business days between

Intermediate

In operations, delivery speed is usually measured in working days, not calendar days. Weekends don’t count, and some warehouses have closure days that also shouldn’t be counted.

The NETWORKDAYS function returns the number of working days between two dates (inclusive), excluding Saturdays and Sundays. You can also pass a list of holiday dates to exclude.

What you need to do:

  1. In cell E2, calculate the number of business days between the order date (C2) and delivery date (D2).
  2. Exclude the warehouse closure dates listed in B12:B14.
  3. Fill the formula down through E9 for all orders.

Tip: Have a look at the related functions to see the exact syntax of NETWORKDAYS.

Need some help?

Hint 1

NETWORKDAYS takes a start date, an end date, and an optional holidays range.

Hint 2

The holiday dates are listed in B12:B14. Make that range absolute before filling down.

Hint 3

After you fill down, compare rows: an order starting on a weekend should still return a smaller number of business days.

Related function(s)
NETWORKDAYSWORKDAYWORKDAY.INTLWEEKDAYTODAY
More exercises
Next business dayIntermediate
AVERAGEIFS multiple criteriaIntermediate
COUNTIFS multiple criteriaIntermediate
Date difference in monthsIntermediate
Back to exercises overview

Exercise