Count times a specific day occurs between two dates

[watertoe]

I'm Looking for a formula that can count times a specific day occurs between two dates.

example:

Count the number of times the 11th day of the month occurs

start date - Oct 11, 2018
end date - Jan 11, 2019

answer would be 4



-thanks for your help

 

[Scott T]

Try

Book1
	A	B	C	D
1	start	10/11/2018		answer
2	end	1/11/2019		4
Sheet1

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=11))

 

[CatyH]

This is a FUN question! Here's what I did...
A1 = StartDate
B1 = EndDate
C1 = NumberDay (e.g. 11)

A3 = YEAR(A1)


In Column E:
E1 = MONTH(A1)
E2 = E1 + 1 (and fill down as needed to E...)

In Column G:
G1 = DATE($A$3,E1,$C$1) (and fill down as needed to G...)

In Column H:
H1 = DAY(G1) (and fill down as needed to H...)

In column I:
I1 = IF(AND(G1>=$A$1,G1<=$B$1,$C$1=H1),1,0) (and fill down.... etc)

Then your sum of column I would be the number of times that day occurred in that time span.

 

[CatyH]

instead of putting "11" in formula, can you do a cell ref so user can enter the number they want? Does that still work?
e.g. =SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=D1)) [where D1 is user-input for day desired]

 

[Scott T]

instead of putting "11" in formula, can you do a cell ref so user can enter the number they want? Does that still work?
e.g. =SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=D1)) [where D1 is user-input for day desired]

Yes that would work.

 

[watertoe]

instead of putting "11" in formula, can you do a cell ref so user can enter the number they want? Does that still work?
e.g. =SUMPRODUCT(--(DAY(ROW(INDIRECT(B1&":"&B2)))=D1)) [where D1 is user-input for day desired]

Thank you everyone for your help, this has been a great solution!