Between 2 dates, Count number of times specific day of month occurs

[XLBeginnr]

Trying to work out how many specific dates occur between two dates, for example:

Start Date: 1/1/2020
End Date: 19/1/2021

Specific Date: 20th of month

Returns: 12

I've found this formula from the post below, but it only works for the same year and doesn't factor in differing years.

=1+MONTH(B2)-MONTH(B1)-(DAY(B2)<20)-(DAY(B1)>20)

Between 2 dates, Count number of times specific day occurs

Would like to have a formula that counts the number times a specific day of the month occurs between two dates. For example: Starting Date: TODAY End Date: April 5th How many 20th of the months occur between the two dates? A simple formula to count months isn't going to account for where you...

www.mrexcel.com

 

[Bo_Ry]

Please try
=DATEDIF(EOMONTH(B1,0)+1,EOMONTH(B2,-1)+1,"m")+(DAY(B1)<21)+(DAY(B2)>19)

 

[XLBeginnr]

Please try
=DATEDIF(EOMONTH(B1,0)+1,EOMONTH(B2,-1)+1,"m")+(DAY(B1)<21)+(DAY(B2)>19)

Thanks, works perfectly.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C
1		01/01/2020	20
2		19/01/2021	12
List

Cell Formulas
Range	Formula
C2	C2	=1+DATEDIF(B1,B2,"m")-(DAY(B1)<C1)-(DAY(B2)>C1)

 

[Tetra201]

@ Bo_Ry: your formula stumbles if the end date is 20-Jan-2020 -- it returns #NUM!.

@ Fluff: your formula stumbles if the end date is 20-Jan-2020 -- it returns 0; with the end date of 21-Jan-2020, it returns -1.

Here is my take on it:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=20))

Or a non-volatile version:

=SUMPRODUCT(--(DAY(ROW(INDEX(A:A,B1):INDEX(A:A,B2)))=20))

 

[XLBeginnr]

@ Bo_Ry: your formula stumbles if the end date is 20-Jan-2020 -- it returns #NUM!.

@ Fluff: your formula stumbles if the end date is 20-Jan-2020 -- it returns 0; with the end date of 21-Jan-2020, it returns -1.

Here is my take on it:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=20))

Or a non-volatile version:

=SUMPRODUCT(--(DAY(ROW(INDEX(A:A,B1):INDEX(A:A,B2)))=20))

Yes! Thanks so much, the non-volitile one works great. Thanks so much!

 

[Tetra201]

You are welcome.

 

[Bo_Ry]

It looks fine here and it doesn't need slow array calculation.

Book1
	B	C
1	1/1/2019
2
3	1/1/2020
4	1/10/2020	12
5	1/19/2020	12
6	1/20/2020	13
7	1/21/2020	13
8	2/1/2020	13
9	2/10/2020	13
10	2/19/2020	13
11	2/20/2020	14
12	2/21/2020	14
13	3/1/2020	14
14	3/10/2020	14
15	3/19/2020	14
16	3/20/2020	15
17	3/21/2020	15
18	4/1/2020	15
19	4/10/2020	15
20	4/19/2020	15
21	4/20/2020	16
22	4/21/2020	16
Sheet2

Cell Formulas
Range	Formula
C4:C22	C4	=DATEDIF(EOMONTH($B$1,0)+1,EOMONTH(B4,-1)+1,"m")+(DAY($B$1)<21)+(DAY(B4)>19)

 

[Bo_Ry]

I see, Problem when the start date and end date on the same month

Book1
	B	C
1	1/1/2020
2
3	1/1/2020	0
4	1/10/2020	0
5	1/19/2020	0
6	1/20/2020	1
7	1/21/2020	1
8	2/1/2020	1
9	2/10/2020	1
10	2/19/2020	1
11	2/20/2020	2
12	2/21/2020	2
13	3/1/2020	2
14	3/10/2020	2
15	3/19/2020	2
16	3/20/2020	3
17	3/21/2020	3
18	4/1/2020	3
19	4/10/2020	3
20	4/19/2020	3
21	4/20/2020	4
22	4/21/2020	4
Sheet2

Cell Formulas
Range	Formula
C3:C22	C3	=DATEDIF(EOMONTH($B$1,0)+1,EOMONTH(B3,0)+1,"m")+(DAY($B$1)<=20)-(DAY(B3)<20)

 

[Tetra201]

I see, Problem when the start date and end date on the same month...

Not only. For the OP range of dates, how many 30th's of the month? 31st's of the month?