Excel – Number of Sundays – A better Answer

I looked up how to count the number of Sundays between two dates…. the so-called answer shocked me.  There are a couple of popular answers, but they are horribly inefficient.

Let me show you a better way…..

 

Solution

Let’s say we have the following cells:

	A	B
1	Start	1/1/2025
2	End	5/1/2025
3	Which Day	1

We will follow the Excel convention, where Sunday = 1, to find the number of “1” days (cell B3) between 1/1/2025 (cell B1) and 5/1/2025 (cell B2).

Between the two dates:

=INT(B2-(B1+B3-WEEKDAY(B1)+7))+1

Correctly returns 17

Between and including the two dates:

=INT(B2-(B1+B3-WEEKDAY(B1)+7))+1+IF(WEEKDAY(B1)=B3,1,0)

Correctly returns 17.  If we were to change B1 to 1/5 (a Sunday), the result would be 16 without the extra IF term.

 

For a more detailed explanation…

Quick Background on Excel Dates

Excel stores a date as a “dateserial”… an integer number of days since 1/1/1900.

So today’s dateserial (2/20/25) is 45,708.

If I add one, 45,708 + 1 = 45,709 = 2/21/2025

Time is a decimal value between integers.

For example, 5 PM = 1700 hours.  So 2/20/2025 + (17/24) = 45,708.7083

To add days, you add an integer number.  2/20/2025 + 5 = 2/25/2025

To find the number of days between the two, just subtract 2/25/2025 – 2/20/2025 = 5.

 

Detailed Explanation

The first thing we need to do is find the first Sunday within the range.

The WEEKDAY function returns a number 1-7 indicating the day of the week for a given date, where 1=Sunday, etc.

If we subtract B3 (the day we are looking for) from the weekday of the start date, we get the number of days offset from the start date.

=WEEKDAY(B1)

Returns “4” because 1/1/2025 is on a Wednesday

=B3-WEEKDAY(B1)

Returns -3, which is an offset. If we go BACK 3 days from 1/1, that’s the last Sunday in Dec, 2024.

=B3-WEEKDAY(B1)+7

By adding 7, we ensure that we always have a positive offset from the start date (B1).

=B1+B3-WEEKDAY(B1)+7

In this case, -3+7 = 4, and sure enough, B1 (start date) is on a Wednesday, if we add 4 days to B1, we get 1/5, which is in fact the first Sunday on or after B1.

Now that we have identified the correct starting point, we can simply subtract from the end date to get the total number of days.

=B2-(B1+B3-WEEKDAY(B1)+7)

Returns 116.  Since =B2-B1 returns 120, this checks out…  Remember that our offset to Sunday was 4 days.

Since every Sunday is exactly 7 days from the previous, we can simply divide by 7.

=INT(B2-(B1+B3-WEEKDAY(B1)+7))

Returns 16.  We use the INT function to return an integer…we don’t care about the fractional number of days between the last Sunday and the end date.

However, we have a problem… there are 17 Sundays between the two.  Remember that we subtracted (and therefore excluded) the first Sunday…

=INT(B2-(B1+B3-WEEKDAY(B1)+7))+1

Returns 17.  We can simply add 1 to put back the first Sunday that we subtracted.

We can confirm this by changing B2 to 5/4/2025 (a Sunday), and the same formula correctly returns 18.

CAVEAT:  If our starting date is also Sunday, for example 1/5, the formula reports that there are only 17 Sundays between 1/5 and 5/4.  This is technically correct, as now the first Sunday is no longer BETWEEN the two dates.  However, this might not be the behavior you expect.  If not, just add an IF:

=INT(B2-(B1+B3-WEEKDAY(B1)+7))+1+IF(WEEKDAY(B1)=B3,1,0)

The nice thing about this formula is that we can count any weekday we want.  For example, we can count Fridays by changing B3 to 6.

I hope you find this useful, and happy Excelling!