Count dates by day of week

Count dates by day of the week

Excel makes it possible to view a list of dates and evaluate with of them is a particular day of the week.

If there is a column filled with several dates, using excel, you can identify how many days on that date list are Mondays or Saturdays as the case may be.

Formula

=SUMPRODUCT(--(WEEKDAY(dates)=day_num))

or

=SUMPRODUCT(--(WEEKDAY(dates,2)=day_num))

How to Count Dates by Day of Week

Using the formula, it is easy to count dates by day of the week. First, you need to get familiar with the parameters for the formula.

In the formula, there are two major functions and they are the SUMPRODUCT and WEEKDAY functions. In this case, they go an extra mile beyond the capacity of the COUNTIF or COUNTIFS function and hence are the best options for counting dates by day of the week.

Defining the parameters:

  • dates: This represents a named range specifying what column the date list that is to be tested is on
  • day_num: This represents the number list for the days of the week i.e. 1,2,3 …
  • The double negation in the formula is necessary because SUMPRODUCT does not work with texts or with Booleans but with numbers. This coerces the result to 0 or 1 instead of True or False.
  • WEEKDAY function accepts two arguments – dates, 2. The value 2 is a predefined and yet optional argument. It causes the WEEKDAY function to return numbers 1-7, corresponding with Monday to Sunday.
    Without the second argument, the arrangement might be distorted.

If there are any blank spaces on the dates list, excel will throw an error. In cases like this replace the formula with:

=SUMPRODUCT((WEEKDAY(dates,2)=E4)*(dates<>"")).

The *(dates<>””)) section of the formula cancels empty cells

Example 1

Let us assume that you received a sheet showing the dates when official transactions were made for company stationeries, and you need to identify what weekdays the purchases were made.

The following steps should be taken.

  1. Paste the date list

Figure 1 – List of dates to be sorted

  1. Prepare the Weekday table – showing the weekday, the counter and the count for the number of times a weekday appeared on the date list.

Figure 2 – Showing the Weekday table containing weekday list, counter, and count

  1. Declare the day number range – showing the cells which contain dates to be used

Figure 3 – Showing the day number range containing the date cells to be included in the sorted

  1. Slot in the Count date by day of week formula:

=SUMPRODUCT(--(WEEKDAY(dates)=day_num))

Figure 4 – Showing the result of the excel REPLACE function

  1.     The result showing how to count date by day of the week

Figure 5 – Showing the result of count date by day of the week

Example 2

If the above illustration contains an empty date cell, there will be an error.

How to fix it:

Use

=SUMPRODUCT((WEEKDAY(dates,2)=E4)*(dates<>"")).

  1.     The instance with an empty cell

Figure 6 – Showing empty cells at A7 and A10

  1.     Slot in the formula

Figure 7 – Showing the formula that excuses empty cells

  1.     Result

Figure 8 – Showing result after empty cells have been removed

Note

  • Failure to use the correct number of parenthesis will cause errors.
  • It is also important to note that the ‘dates’ part of the formula, remains constant all through the evaluation. If it changes, then the result will be erroneous.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar