Count number of times 1 specific day occurs in a date range

[matthewpaul12]

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

Hi, not sure if anyone will see this, but here goes. This formula is exactly what I need as well, except that it doesn't account if the year is not the same (i.e. beginning date of 10/1/17, end date of 11/26/18, trying to account for number of times 15th day of month occurs). This formula results in 2 (I'm assuming it's calculating under the assumption Oct 2018, not 2017), when it should be 14. Any help would be much appreciated!

 

[Phuoc]

Re: Between 2 dates, Count number of times specific day occurs

Starting Date in A2

End Date in B2

Like this:

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

 

[matthewpaul12]

Hi, not sure if anyone will see this, but here goes. I am trying to calculate the number of times a specific day (number) occurs in a date range.

I started with the formula: =1+MONTH(B2)-MONTH(B1)-(DAY(B2)<15)-(DAY(B1)>15)

This formula is exactly what I need (most recent date is B2, beginning date is B1), except that it doesn't account if the year is not the same (i.e. beginning date of 10/1/17, end date of 11/26/18, trying to account for number of times 15th day of month occurs). The above formula results in 2 (I'm assuming it's calculating under the assumption Oct 2018, not 2017), when it should be 14. Any help would be much appreciated!

 

[AlanY]

try this modified version

=(1+MONTH(B2)-MONTH(B1)-(DAY(B2)<D1)-(DAY(B1)>D1)+12*(YEAR(B2)-YEAR(B1)))

 

[matthewpaul12]

Re: Between 2 dates, Count number of times specific day occurs

Think this works. Thank you so much! You are a lifesaver!

 

[Fluff]

Re: Between 2 dates, Count number of times specific day occurs

@matthewpaul12
In future please do not post the same question multiple times. (rule 12 here: Forum Rules).

I have merged you 2 threads.

 

[matthewpaul12]

Re: Between 2 dates, Count number of times specific day occurs

Starting Date in A2

End Date in B2

Like this:

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

This works 90% of the time, but how could it be adjusted, so that it doesn't include it if either of the days are the same day as one of the end points in the range?

In other words, the formula above gives a result of "3" counting the number of times the 7th day of the month occurs between 7/4/18 and 9/7/18