Calculating the number of work days between two dates for unusual shifts

Excel 365
When calculating the difference between two dates, you may want to exclude weekends and holidays. You can see how to calculate the number of Working Days between two dates for the usual, standard working week where weekends are Saturdays and Sundays.

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):

Number of Work Days for unusual shifts in Excel 2016

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.

Work Days for unusual shifts in Excel 2016
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.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more