We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates?

We’ll combine four functions to solve this problem. See the image below which has a start date and an end date. This calculation is inclusive, so if both dates were Sundays they would both be counted. The link to an example file is at the bottom of the post.

The formula in B3 is

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=1)*1)

Let’s unpack this formula which involves a couple of advanced techniques. Let’s start in the middle which is the part that does the hard work.

INDIRECT(B1&":"&B2)

The INDIRECT function is a sort of “function of last resort” if you can’t do something with Excel’s other functions you can usually get the INDIRECT function to provide a solution.

In this case we need to create a flexible listing of dates between 1/1/2017 and 31/12/17. The listing needs to be based on the entries in the two input cells.

Before we look at INDIRECT I need to explain something about Excel and dates. If you have worked with Excel for long enough you would have seen an unformatted date. If I remove the formatting from the above image you will see what I mean.

Excel stores dates as numbers. The numbers shown relate to those two dates. How a date is displayed is determined by the format you apply to the cell or range.

Microsoft decided to start the calendar on 1 January 1900 – that was day 1, every day since then has been incremented by 1. So 1/1/2017 is day 42736.

The INDIRECT function uses text to create references. If I select B1&”:”&B2 in the formula and press the F9 function key Excel will display the result of that part of the formula – see image below.

The & symbol joins text together. When you refer to a date in a text formula Excel will use the underlying number, not the displayed format, hence the result of

"42736:43100"

The INDIRECT function converts that text string into a reference. A reference like that will be converted into the row reference 42736:43100.

That reference is not useful by itself but the INDIRECT is within the brackets of a ROW function. The ROW function returns the row number for a reference.

ROW(INDIRECT(B1&":"&B2))

This part of the formula generates a series of sequential numbers from 42736 to 43100 inclusive. Not all functions can handle this type of formula. It is usually only found in array formulas but SUMPRODUCT will cope with it.

WEEKDAY(ROW(INDIRECT(B1&":"&B2)))

The WEEKDAY function works with dates. It converts those sequential numbers into individual dates and returns the weekday numbers (1 to 7) for each date. The WEEKDAY function returns 1 for a Sunday, 2 for Monday, all the way to 7 for Saturday.

(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=1)

This part of the formula return TRUE for every weekday that equals 1 – Sunday is 1. Every other date returns FALSE.

(WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=1)*1

The final part of the formula multiplies all the TRUEs and FALSEs by 1. This converts TRUE into a 1 and FALSE into zero, just like binary because it is binary.

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B1&":"&B2)))=1)*1)

Finally the SUMPRODUCT adds up all the 1s to count all the Sundays in the date range.

Counting Weekend Days

If you wanted to count how many weekend days between two dates you could modify the formula slightly to achieve it.

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B1&":"&B2)))={1,7})*1)

I have changed 1 to {1,7}. This now returns a TRUE for Sunday (1) or Saturday (7).

The braces or curly brackets around entries is called array syntax. Not many functions can use array syntax, but SUMPRODUCT can.

Download the Excel file at link below.

Count_Sundays