Working days around Easter
PremiumThe office is closed on the days in E2:E4. In C2, count the working days from A2 to B2 with NETWORKDAYS, and give it E2:E4 as the holidays.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start | End | Working days | Office closed | ||
| 2 | Mar 25, 2024 | Apr 5, 2024 | Mar 28, 2024 | |||
| 3 | Mar 29, 2024 | |||||
| 4 | Apr 1, 2024 | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock NETWORKDAYS, WORKDAY and WEEKDAY and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for admin jobs
Why this works
March 25 to April 5 is 10 weekdays. The third argument is a list of dates to skip as well, and all three closed days fall on weekdays, so NETWORKDAYS gives 10 - 3 = 7. A holiday that falls on a weekend would not be subtracted twice.
Common mistake: Subtracting the holidays by hand, like NETWORKDAYS(A2,B2)-3. When the dates change, a closed day may fall outside the period or on a weekend.
The answer
- C2
=NETWORKDAYS(1A2,2B2,3E2:E4)
In plain words: Count the working days from 1the start date in A2 to 2the end date in B2, 3leaving out the closed days in E2:E4.