Answer:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"DDDD")="Sunday"))
‘where B1 is start date; B2 is end date.
Excuse me? What it says?
Before we start, let’s remind you the fundamental basic about Date in Excel.
“Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.” From Excel Help
In this sense, we can easily calculate the number of days between two dates by simple subtraction:
B1 = 01/04/2014
B2 = 12/04/2014
The difference will be:
= B2–B1
which gives you a result of 11.
You may also use DATEDIF function that gives you more options in excluding/including number of years/months.
How about if you want to know how many “Sunday” between two dates? There is no function for that. However, a combination of functions would lead you there. This is why Excel can be so powerful.
Here’s the formula:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"DDDD")="Sunday"))
Let’s explore the formula inside out
B1&”:”&B2
Remember that 01/04/2014 and 12/04/2014 are actually a number of 41730 and 41741 respectively. By concatenating them with a semi colon, it gives “41730:41741″ as a result. However the result is a text string that cannot be used directly as a reference for the subsequent ROW function.
ROW(“41730:41741”) will not be accepted by Excel as the argument of ROW must be a reference.
Therefore we need to enclose the “41730:41741” with INDIRECT
INDIRECT(B1&”:”&B2) turns the resulting text string into a reference (41730:41741)
ROW(INDIRECT(“41730:41741”)) yields ROW(41730:41741)
ROW returns the row number of a reference. For example, ROW(A1) or ROW(1) returns 1. ‘Note: Without column reference means whole row reference
- If reference is omitted, it is assumed the position of the cell in which the ROW function is applied to.
- If reference is a range of cells, it returns the rows number as an array.
In our example,
ROW(41730:41741) gives an array of
{41730;41731;41732;41733;41734;41735;41736;41737;41738;41739;41740;41741}
This is the list of sequential serial numbers from the start date to the end date, isn’t it?
Text(Value,”DDDD”) turns the above array into another array of
{“Tuesday”;”Wednesday”;”Thursday”;”Friday”;”Saturday”;”Sunday”;”Monday”;”Tuesday”;”Wednesday”;”Thursday”;”Friday”;”Saturday”}
‘Note: Text function converts a value into a specific formatted text. In our example, it converts Date (value) into corresponding Day (text).
Now we basically have a list of “Days” between the two specified dates in an array.
By putting a logical text =“Sunday” at the end of the array
{“Tuesday”;”Wednesday”;”Thursday”;”Friday”;”Saturday”;”Sunday”;”Monday”;”Tuesday”;”Wednesday”;”Thursday”;”Friday”;”Saturday”}=”Sunday”
It compares each value in the array with “Sunday” and returns FALSE if the value is not equal to “Sunday”; returns TRUE if it is equal to “Sunday”.
As a result, we have an array of TRUE or FALSE:
{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} ‘Note: There is only one TRUE in the array as there is only one “Sunday” between 01/04/2014 and 12/04/2014
Putting double negative (- -) in front of the array to turn FALSE to 0; TRUE to 1
{0;0;0;0;0;1;0;0;0;0;0;0} ‘Tips: Applying other mathematical operations like +0 or *1 would yield the same result
The final step is to count the number of 1 in the array, which essentially means to have the SUM of the array. SUMPRODUCT does this job nicely as it can handle array formula.
=SUMPRODUCT({0;0;0;0;0;1;0;0;0;0;0;0})
gives you a result of 1, that means there is ONE Sunday between 01/04/2014 and 12/04/2014.
Now does the formula make sense to you?
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"DDDD")="Sunday"))
Please feel free to test the formula by changing the dates / day.
Tips:
- To make the formula more flexible, do not hard copy the day you want to look at. E.g. input Sunday in A3, replace =“Sunday” in the formula with =A3. In this way, you may count different days (Monday to Sunday) easily by changing the value in A3.
- If you prefer short day, i.e. “Sun” instead of “Sunday”, replace “DDDD” in the formula with “DDD”.
This post is inspired by the genius solution provided by Aladin Akyurekin in the following thread on mrexcel forum.
You may also like an alternative solution HERE.
wmfexcel 
Thank you John, but why does it not work if ‘Row’ function is replaced with ‘Column’. Should not they work in same way?
LikeLike
Thanks for a great solution. I am using it to count number of weeks within a given month for account purposes where Sunday is the end of the week and gets attributed to the month that Sunday falls in.
Formula works great with one exception – If the end date falls on a the day you are trying to find a count of. Just noticed this due to 01/01/2017 being a Sunday.
Example:
A1 = 12/01/2016
A2 = 01/01/2017
Formula: =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1&”:”&A2)),2)=7))
Gives 5 Sundays in December when there are only 4. This is because 01/01/2017 is a Sunday.
To solve this you just subtract 1 from the end date so your formula is now looking for the following:
Number of days between start date and end date, excluding the end date.
Solution Example:
A1 = 12/01/2016
A2 = 01/01/2017
Formula: =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1&”:”&(A2-1))),2)=7))
LikeLike
Hi Sarah
You are right. The formula counts both start and end dates inclusively. Glad that you managed to revise the formula to suit your need.
Cheers,
LikeLike
This was SO helpful and just saved me – thank you!
LikeLike
You are welcome! Glad it helps. 😀
LikeLike
Many thanks, this site is extremely practical.|
LikeLike
Thanks for your kind words! Glad to know you like it！
LikeLike
A1: Start Date, B1: End Date
NETWORKDAYS.INTL(A1,B1,”1111110″)
LikeLiked by 1 person
Hi John,
Nice twist. This is even better! Thanks for your input!
With NETWORKDAYS.INTL, this problem can be easily solved even for multi days, e.g. Sat and Sun, by modifying the text string in the third argument to “1111100”
Cheers,
LikeLiked by 1 person
=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula
LikeLiked by 1 person
Hi Chandra,
You formula is simple and Awesome! Nice use of NETWORKDAS.INTL function (for Excel 2010 or later).
Thanks for sharing! 🙂
For those who are not familiar with the function, the third argument basically tells excel which day in a week we want to count. 11=Sunday, 12=Monday, 13=Tuesday, 14=Wednesday…,17=Saturday.
Please note that the function itself is not for counting how many Monday/Tuesday… fallen in a period.
If you want to learn more about the NETWORKDAYS.INTL function, I would recommend the following blogpost:
http://excelunplugged.com/2015/01/27/workday-and-workday-intl-in-excel/
written by Gašper Kamenšek.
LikeLike
Hi MF
This is a hard work to build this formula, =SUMPRODUCT(–(TEXT(ROW(INDIRECT(B1&”:”&B2)),”DDDD”)=”Sunday”)) but I’ve got a result of 1 Sunday in the indicated time range, not 11.
Also, inst that easier to perform something like this =(B2-B1)/7
LikeLike
Hi Igor,
The answer 1 Sunday is expected, as there is only one Sunday in the given period (from 1st Apr, 2014 to 12th Apr, 2014). Isn’t it?
(B2-B1)/7 will not give you the answer expected. For example, when
B2 = 30/10/2015
B1 = 29/10/2015
then how many “Friday” in the given period? The answer should be 1, which (B2-B1)/7 won’t able to give you the correct answer to the question.
Cheers,
LikeLike
Yes, it is just one Sunday.
The reason I mentioned it you said above: “which gives you a result of 11.” so I thought there was a mistake in above explanation.
That is correct for short period (B2-B1)/7 does not work the same way, but if you need something quick for long period like i.e. start date is May 14/12 end date Oct 26/15, the result will be exactly 180.0 Sundays for both formulas. For more precise calculations I would go with your formula.
Thanks Fung.
LikeLike
Hi Igor,
I know what you mean…. I just realized that I had a typo in the post (now being revised)…
The result of 11 is actually from B2-B1. Sorry for the confusion. ;p
Cheers,
LikeLike
If you are interested in learning How to Use SUMIF between Two Dates using VBA
http://www.exceltip.com/tips/how-to-use-sumif-between-two-dates-using-vba-in-microsoft-excel-2010.html
LikeLike