Today we are going to discuss about the method to find number of Sunday that fall on last date of month in the given period. Lot to learn, let's dive in.
Formula in C6 cell is,
=SUMPRODUCT((TEXT(EOMONTH(DATE(C2,1,1),SEQUENCE(12*(C3-C2+1),,0)),"ddd")=B6)*1)
Let me de-construct this into digestible blocks,
EOMONTH function helps us to find the End of Month date for given date.
Arguments of EOMONTH function,
=EOMONTH(start_date,months)
start_date can be any date, (ex., 24/06/2024)
if you feed 0 in months, it will deliver date at the end of Jun month. (30/06/2024)
if you feed 1 in months, it will deliver date at the end of Jul month. (31/07/2024)
if you feed -1 in months, it will deliver date at the end of May month. (31/05/2024)
In our requirement, start year is 2017 and end year is 2020. So, 48 months in between these years.
we are going to form the sequence of numbers from 0 to 47 to feed into months argument of EOMONTH function.
=SEQUENCE(12*(C3-C2+1),,0)
We hardcoded number of months in a year (12), since this will not change.
Starting number of the sequence is zero.
Result of this construction is,
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47}
=EOMONTH(DATE(C2,1,1),SEQUENCE(12*(C3-C2+1),,0))
Here, based on the start year in cell C2, we constructed a start date for EOMONTH function,
and for months, we fed sequence of numbers which we created.
TEXT function helps us to get the days of end of months in "ddd" format.
If we compare these days with the given day in B6 cell, then output will look like below,
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Any mathematical operation will convert the Boolean values into zero and one. So, we multiply this with 1.
Then, simply sum the result using SUMPRODUCT to get the number of Sundays that fall on end of month.
Now, data validation in B6 cell will help us to get the count of any days within given years.
Bonus Method:
Bit longer compared to previous one,
=SUMPRODUCT((WEEKDAY(EOMONTH(DATE(C2,1,1),SEQUENCE(12*(C3-C2+1),,0)),2)=MATCH(B6,$B$10:$B$16,0))*1)
Instead of comparing the days, here we compare the weekdays of end of month with the weekday of given day.
Two points about your formula... first, since you are using the SEQUENCE function, the assumption is you are using a version of Excel that supports dynamic arrays; hence, you can use the SUM function in place of the SUMPRODUCT function... second, you can shorten your formula be another 3 characters by removing the ",,0" from your SEQUENCE function (so it starts counting at 1) and change the day's argument of 1 in the DATE function to 0. Here is what your formula would look like with these two changes...
=SUM((TEXT(EOMONTH(DATE(C2,1,0),SEQUENCE(12*(C3-C2+1))),"ddd")=B6)*1)
And, while your formula is the one I would use, I thought you might find this alternate formula to be of interest...
=SUM(0+(TEXT(DATE(SEQUENCE(C3-C2+1,,C2),SEQUENCE(,12,2),1)-1,"ddd")=B6))