## Calculate number of a specific day between two dates `=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.

http://www.mrexcel.com/forum/excel-questions/762164-counting-specific-days-specific-month-between-two-dates.html

You may also like an alternative solution HERE. An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

### 16 Responses to Calculate number of a specific day between two dates

1. Jawad Mansoor says:

Thank you John, but why does it not work if ‘Row’ function is replaced with ‘Column’. Should not they work in same way?

Like

2. Sarah W Johnson says:

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))

Like

• MF says:

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,

Like

3. Heather says:

This was SO helpful and just saved me – thank you!

Like

• MF says:

You are welcome! Glad it helps. 😀

Like

4. nba 2k16 mtdb packs says:

Many thanks, this site is extremely practical.|

Like

• MF says:

Like

5. John Jairo V says:

A1: Start Date, B1: End Date
NETWORKDAYS.INTL(A1,B1,”1111110″)

Liked by 1 person

• MF says:

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,

Liked by 1 person

6. Chandra Mohan says:

=ED-SD-NETWORKDAYS.INTL(SD,ED,12)+1, try this formula

Liked by 1 person

• MF says:

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.
http://excelunplugged.com/2015/01/27/workday-and-workday-intl-in-excel/
written by Gašper Kamenšek.

Like

7. Igor says:

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

Like

• MF says:

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,

Like

• Igor says:

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.

Like

• MF says:

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,

Like

8. ashish says:

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

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.