Between 2 dates, Count number of times specific day occurs

[abaxter]

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 are in the current month. For example, the formula should calculate 3 based on today's date (Jan 14th) but it should only calculate 2 if today were Jan 21st.

Thanks!
Andrew

 

[ZVI]

For the dates in b1:b2 try this: =1+MONTH(B2)-MONTH(B1)-(DAY(B2)<20)-(DAY(B1)>20)

 

[abaxter]

For the dates in b1:b2 try this: =1+MONTH(B2)-MONTH(B1)-(DAY(B2)<20)-(DAY(B1)>20)

Awesome!!!

 

[hhekker]

What is the formula that ignores the year? For example, the number of the 27th day between 5 October 2020 and 27 January 2021.

 

[ZVI]

Hi,

What result do you expect 4 or 8?

Try this formula that actually takes years into account and returns 4:
=1+MONTH(B2)+YEAR(B2)*12-MONTH(B1)-YEAR(B1)*12-(DAY(B2)<27)-(DAY(B1)>27)

for result = 8 use that one:
=ABS(1+MONTH(B2)-MONTH(B1)-(DAY(B2)<27)-(DAY(B1)>27))

 

[hhekker]

Hi,

What result do you expect 4 or 8?

Try this formula that actually takes years into account and returns 4:
=1+MONTH(B2)+YEAR(B2)*12-MONTH(B1)-YEAR(B1)*12-(DAY(B2)<27)-(DAY(B1)>27)

for result = 8 use that one:
=ABS(1+MONTH(B2)-MONTH(B1)-(DAY(B2)<27)-(DAY(B1)>27))

I expect 4 as a result. I have copied the formula and it works.

Thank you for your help.

 

[ZVI]

Glad it is working as expected!
Welcome to MrExcel Board!