One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.
Before we look at the dynamic array solution here is another formula that works in all versions that counts the number of Sundays between two dates. It is shorter and simpler than my previous solution.
The formula in cell D1 is.
=A31-A1-NETWORKDAYS.INTL(A1,A31,11)+1
The start date is in cell A1. The end date is in cell A31.
The NETWORKDAYS.INTL function allow you to ignore weekends and count specific days between two dates. The ,11 on the end specifies ignoring Sundays. The image below shows the numbers used for the other days of the week.
The NETWORKDAYS.INTL function calculates the number of days between the two dates without Sundays. We can deduct that number from the total number of days between the two dates and add 1 to get the correct number for Sundays.
Remember days calculations are exclusive. So the 31 March less the 1 March give 30 days, but we want to include both days so we add 1 to the result.
The issue with this solution is that the user needed to figure out what number to use for Sunday. The solution below is more user friendly.
Dynamic array solution
In the old post I used a hack with the INDIRECT function to get a list of dates. That is much easier now with dynamic arrays. See image below.
The formula in cell D2 is.
=LET(d,SEQUENCE(A31-A1+1,,A1),SUM((TEXT(d,”ddd”)=”sun”)*1))
The LET function enables the use of variables within a formula.
The d variable captures the list of dates created using the SEQUENCE function. The d variable is used within the TEXT function to find the matching Sunday dates. The SUM function adds up all the correct matches and returns the result.
Custom Function
We can simplify this calculation by creating a custom function. You can learn more about custom functions at this post.
The formula to test the custom function is shown below in cell D3.
The formula is.
=LAMBDA(start,end,txtday,LET(d,SEQUENCE(end-start+1,,start), SUM(IF(TEXT(d,”ddd”)=txtday,1,0))))(A1,A31,”sun”)
Cell A1 is passed to the start argument.
Cell A31 is passed to the end argument.
“sun” refers to Sunday and is passed to the txtday argument. Much easier for users to enter the abbreviated day name rather than a number that represents that day.
The final custom function in use is shown below in cell D4.
The Name dialog used to create the custom function is shown below.
Custom functions simplify complex calculations and speed up formula creation.
Please note: I reserve the right to delete comments that are offensive or off-topic.