How to Count Dates by Day of Week in Excel

This post will guide you how to count days of week in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by day of week in Excel.

Count Dates by Day of Week Through Formula


Sometimes, you may be want to count days of week in a given date range or between two dates. And you need to build a complex formula to work with your data. To count dates by weekday(such as: Mondays, Tuesdays, etc), and you can use the SUMPRODUCT function in combination with the WEEKDAY function to create a formula in Excel.

For example, your date range is B1:B6, and you want to count dates by weekday, just using the following formula:

=SUMPRODUCT(–(WEEKDAY($B$1:$B$6)=D1))

count dates by days of week1

LETS SEE THAT HOW THIS FORMULA WORKS:

=WEEKDAY(B1:B6)

count dates by days of week2

The formula WEEKDAY returns a number between 1 and 7 based on a particular day of the week. Such as: Sunday is 1, Monday is 2, Thuesday is 3, Wednesday is 4, Thursday is 5, Friday is 6 and Saturday is 7.

This formula returns an array like this:

{1;4;5;6;2;4}

count dates by days of week3

=WEEKDAY($B$1:$B$6)=1

count dates by days of week4

Then the numbers returned by WEEKDAY formula are compared to the given day value of weekday, returns another array result like this:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

count dates by days of week5

=–(WEEKDAY($B$1:$B$6)=1)

count dates by days of week6

You still need to convert the TRUE or FALSE values to 1 and 0 values using the double negative operator. And returns the below array result:

{1;0;0;0;0;0}

count dates by days of week7

The SUMPRODUCT function just sums all values of an array.

 

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…

 

Related Posts

How to Count Occurrences in Entire Workbook in Excel
count occurrences in entire workbook7

This post will guide you how to count number of occurrences in whole workbook in Excel 2013/2016 or Excel office 365. How do I count how many times a string is repeated in all worksheets in Excel. You may be ...

How to Count Numbers Nth Digit Equals to Specific Number in Excel
count number n digit number7

This post will guide you how to count numbers where the Nth digit equal to a specific number in Excel 2013/2016 or Excel office 365. How do I count of numbers in a given range where the Nth digit is ...

How to Count Matches between Two Columns in Excel
count matches between two columns5

This post will guide you how to Count the number of matches between two columns using a formula in Excel 2013/2016 or Excel office 365. How do I compare two columns within two different ranges or columns and count if ...

How to Count the Cells that Match Two Criteria in Excel
count cells match two criteria2

This post will guide you how to Count Cells that match two or more criteria in the range of dates using a formula in Excel 2013/2016 or Excel office 365.How do I count on two or multiple criteria or you ...

How to Count Dates of Given Year in Excel
count dates of given year7

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

How to Sum Every Nth Column in Excel
Sum Every Nth Column 27

Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. ...

How to Count Cells that do not Contain Errors in Excel
count cell do not contain error5

This post will guide you how to count the number of cells that do not contain errors within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count the number of cells ...

How to Sum Data by Weekday with Different Formulas/Functions in Excel
Sum Data by Weekday 20

To generate a business summary report for further investigation, we can sum income or profits for a date like Monday, Tuesday …etc. to check on which date we have the top income in a week, then we can estimate the ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Calculate Number of Weekends between Two Dates in Excel
calculate number of weekdays3

This post will guide you how to count the number of weekends between two given dates in Excel. How to find out how many weekends are between two dates in Excel. How to count the number of Sundays between two ...

Sidebar