Calculating the number of work days between two dates for unusual shifts
If you have an unusual working week such as weekends on Sundays and Mondays, you can use other formulas in Excel.
For example, you may need to know how many business days fall into November 2018. This calculation should exclude Sundays, Mondays, and holidays.
The NETWORKDAYS.INTL function calculates the difference between two dates, excluding weekend days (see the table below):
As an option, you can specify a range of cells that contain the dates of holidays, which are also excluded. Excel has absolutely no way of determining which days are holidays, so you must provide this information in a separate range.
Weekend number | Weekend days |
---|---|
1 (default) | Saturday, Sunday |
2 | Sunday, Monday |
3 | Monday, Tuesday |
4 | Tuesday, Wednesday |
5 | Wednesday, Thursday |
6 | Thursday, Friday |
7 | Friday, Saturday |
11 | Sunday only |
12 | Monday only |
13 | Tuesday only |
14 | Wednesday only |
15 | Thursday only |
16 | Friday only |
17 | Saturday only |
Note: Unfortunately, this formula doesn’t work for four-day workweeks or for shifts. See How to calculate the number of work days for a four-day workweek.
See also this tip in French: Comment calculer du nombre de jours ouvrables entre deux dates des quarts de travail inhabituels.