< Go Back

Count day of week between dates

If you want to count the days of a week between two specified dates, you can do it automatically with excel. What you need is an array of formula based on several functions that include SUMPRODUCT, INDIRECT, WEEKDAY, AND ROW. The formula looks like this:

Formula

The generic formula to count the days of a week between two dates is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(date1&":"&date2)))=dow))

Explanation

If you want to count how many times a certain day appears between two dates in excel, the problem will be solved automatically. In this formula, we have different ranges of dates to test.

The WEEKDAY function tests if the given number of dates will land on a given day of the week or what is abbreviated as DOW. The function simply returns a number/value between 1 and 7 which is corresponding to a particular day of the week. Note that, with the default setting, 1 is Sunday while 7 is Saturday with the rest of the day falling in consecutively. Number 2 is Monday followed by Tuesday.

In Excel INDIRECT function, the chain of dates, 43101:43115, is interpreted as a row of numbers, therefore it returns the array of serial numbers using Excel Row function

Example

In this example, you have start and end dates and want to count the number of the specified days of the week between these dates given as a number. From the screenshot below, the formula in the highlighted cell (E5) is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B5&":"&C5)))=D5))

As dates in excel are serial numbers and that is what the formula understands. For the 1st January of 2018, the serial number is 43101 and that of January 15, 2018, is 43115.

With the help of INDIRECT function, the chain of dates is supplied in the ROW function, so these dates’ serial numbers are interpreted as row numbers. Thus, the ROW function returns an array of serial numbers that looks like this:

{4310;43102;43103;43104;43105;43106;43107;43108;43109;43110 ;43111;43112;43113;43114;43115}

Then, the WEEKDAY function converts the dates’ serial numbers in the array into weekday numbers. The array looks like this:

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

When this array is tested against the number in cell D5, (5 or Thursday) and double dash sign converted TRUE and FALSE values int 1s and 0s. This is what you get:

=SUMPRODUCT(0;0;0;1;0;0;0;0;0;0;1;0;0;0;0)

=2

Figure 1. Example 1 of SUMPRODUCT+ Function

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