Find number of days in a date range but only for a specific Month

[Aviles]

Hi all,

I'm looking for a formula to find the number of days within a date range, but only for a specific month (in this case, July):

Start Date	End Date	Expected Result (days)
28/06/2019	27/08/2019	31
15/07/2019	15/10/2019	17
01/03/2019	10/07/2019	10

<tbody>
</tbody>

In this instance I'm only looking for the number of days in July, but this will change and I would like to easily change the formula to suit other months.

Thanks in advance.

 

[salim hasan]

Try My Array formula (Ctrl+Shift+Enter)
supposing that your data in cells a1 and b1

=SUMPRODUCT(IF(MONTH(ROW(INDIRECT(A1&":"&B1)))=7,1,0))

 

[Peter_SSs]

For a non-volatile option, you could try this.

Change A1 for a different month.

Excel Workbook
	A	B	C
1	July
2
3	Start Date	End Date	Result (days)
4	28/06/2019	27/08/2019	31
5	15/07/2019	15/10/2019	17
6	1/03/2019	10/07/2019	10
Days

 

[Peter_SSs]

.. or for a non-volatile version of the post 2 formula, and also using a single cell to change the month-of-interest, you could try this.
Note that this also does not require the Control+Shift+Enter confirmation.

Excel Workbook
	A	B	C
1	June
2
3	Start Date	End Date	Result (days)
4	28/06/2019	27/08/2019	3
5	15/07/2019	15/10/2019	0
6	1/03/2019	10/07/2019	30
Days (2)

 

[Aviles]

Thank you, they both worked well. I appreciate the quick responses.

 

[Peter_SSs]

Thank you, they both worked well. I appreciate the quick responses.

You're welcome.