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…