How to count days of week in the range of dates

Excel 365
From time to time, you need to count days of week in some date range or between two dates (see How to count days of week between two dates). Excel proposes different formulas to work with data. It is easy to create the formula you need for this task:

The formula WEEKDAY returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday and 7 = Saturday.

To count days of week for the date range, for example, to count Fridays in the November holidays (range B2:B5), do one of the following:

To use the COUNTIF formula

   1.   Add new column (or row) for the range and insert the formula:

= WEEKDAY (<date>):

Day of week calculation in Excel 2016

   2.   Use the formula COUNTIF (<data range>, criteria) with the following parameters:

= COUNTIF (D2:D5, "=6"):

or

= COUNTIF (D2:D5, "="&F6):

COUNTIF formula in Excel 2016

To use the SUMPRODUCT formula

This formula is more complex, but doesn't need any additional calculation:

= SUMPRODUCT (-- (WEEKDAY (B2:B5) = 6))

or

= SUMPRODUCT (-- (WEEKDAY (B2:B5) = E7)):

SUMPRODUCT formula in Excel 2016

Whenever you put -- (two hyphens) in the front of the parenthesis, it changes all the values TRUE / FALSE in the array in the parenthesis to the values 1 / 0. So:

  • the formula (WEEKDAY (<date range>) = 6) returns the array of TRUE and FALSE,
  • (-- <array>) returns array of 1 and 0 instead of TRUE and FALSE,
  • SUMPRODUCT just sums all values of an array.

See also this tip in French: Comment compter les jours de la semaine dans la plage de dates.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

How to count days of week between two dates

How to count days of week between two dates

From time to time, you need to count days of week between two dates or in some date range (see How to count days of week in the range of dates). Excel proposes different formulas to work with data. It is easy to create the formula you need for this task:
How to calculate the number of work days for a four-day workweek

How to calculate the number of work days for a four-day workweek

When calculating the difference between two dates, you may want to exclude weekends and holidays. Excel proposes a standard formula for calculating the number of workdays for standard, five-day workweek. Also, Excel proposes a formula for calculating the number of workdays for different five-day workweek or six-day workweek.
Calculating the number of work days between two dates for unusual shifts

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

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.

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