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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Expected
Result (days)[/TD]
[/TR]
[TR]
[TD]28/06/2019[/TD]
[TD]27/08/2019[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]15/07/2019[/TD]
[TD]15/10/2019[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]01/03/2019[/TD]
[TD]10/07/2019[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

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.