Count specific month occurrence between 2 dates

[FeedngFrenzy]

Is there a way to count how many times a specific month occurs between two dates? For instance, if we have semi-yearly reviews every April and October, how would I be able to count how many reviews someone has until retirement. I am using today as a base so the data will change and I don't want to update all the records twice a year if I don't have to and they all will be different. Thanks

Edit: I am using Excel 2013 if that helps

 

[Aladin Akyurek]

This "count how many times a specific month occurs between two dates" and this "count how many reviews someone has until retirement" look like different questions. If so, let the would be helpers to tackle them one at a time. Also, it would help to have an illustrative small sample along with the expected result.

 

[FeedngFrenzy]

My fault, I see the issue. Basically I want to see how many April reviews someone will have and how many October ones. I do have two separate columns set for them so I really only need to figure out how to just count one. I can make the necessary adjustments. Sorry about the confusion.

 

[Aladin Akyurek]

My fault, I see the issue. Basically I want to see how many April reviews someone will have and how many October ones. I do have two separate columns set for them so I really only need to figure out how to just count one. I can make the necessary adjustments. Sorry about the confusion.

Still missing the location of data... That said: Control+shift+enter, not just enter...

=SUM(IF(RevieweeRange=Reviewee,IF(DateRange-DAY(DateRange)+1=DATE(2017,4,1),1)))

This counts April 2017 reviews of Reviewee.

 

[FeedngFrenzy]

Still missing the location of data... That said: Control+shift+enter, not just enter...

=SUM(IF(RevieweeRange=Reviewee,IF(DateRange-DAY(DateRange)+1=DATE(2017,4,1),1)))

This counts April 2017 reviews of Reviewee.

I see what I am missing, I don't know how to post an example so I will explain best I can.

In A1 I have today's date.
Column B is their original start date, D has their retirement eligibility date of column B + 20 years.
In F (April) and G (October) is where I want to put how many reviews are left as of today until the date that is in column D.

 

[Aladin Akyurek]

I see what I am missing, I don't know how to post an example so I will explain best I can.

In A1 I have today's date.
Column B is their original start date, D has their retirement eligibility date of column B + 20 years.
In F (April) and G (October) is where I want to put how many reviews are left as of today until the date that is in column D.

Care to provide two rows B and D along with the expected number of "reviews" as of today in F and in G?

 

[Tetra201]

...In A1 I have today's date.
Column B is their original start date, D has their retirement eligibility date of column B + 20 years.
In F (April) and G (October) is where I want to put how many reviews are left as of today until the date that is in column D.

Try these array formulas (to be entered using Ctrl+Shift+Enter, not just Enter):

for the number of April reviews =($D2>=$A$1)*SUM(SIGN(FREQUENCY(IF(MONTH(ROW(INDIRECT($A$1&":"&$D2)))=4,YEAR(ROW(INDIRECT($A$1&":"&$D2)))),IF(MONTH(ROW(INDIRECT($A$1&":"&$D2)))=4,YEAR(ROW(INDIRECT($A$1&":"&$D2)))))))

for the number of October reviews
=($D2>=$A$1)*SUM(SIGN(FREQUENCY(IF(MONTH(ROW(INDIRECT($A$1&":"&$D2)))=10,YEAR(ROW(INDIRECT($A$1&":"&$D2)))),IF(MONTH(ROW(INDIRECT($A$1&":"&$D2)))=10,YEAR(ROW(INDIRECT($A$1&":"&$D2)))))))

 

[inactiveuserps07]

No specific date was given for the April or October reviews, but this would work in general (and is pretty easy to understand):


=YEAR(D1)-(YEAR(A1)+1)+(IF(MONTH(A1)<=4,1,0)+(IF(MONTH(D1)>=4,1,0)))


Explanation:

Find the number of FULL years left (where we know the person will be employed from 1/1 to 12/31 of that year) with the following formula:

YEAR(D1)-(YEAR(A1)+1)

(i.e., January 1 of next year until January 1 of retirement year)

That gives us a GIVEN number of Aprils.

Then we just need to figure out if April has already fallen in the remainder of this year, and, if not, add 1 to the total ...

+(IF(MONTH(A1)<=4,1,0)

... and then if an April will remain in the partial year of the retirement, and if so, add 1 to the total ...

+(IF(MONTH(D1)>=4,1,0))

If you had a specific date that is used for the review, you'd just replace MONTH(A1) and MONTH(D1) with A1 and D1, respectively; and then replace the 4 and 4 with the actual review date.

 

[Tetra201]

No specific date was given for the April or October reviews, but this would work in general (and is pretty easy to understand):

=YEAR(D1)-(YEAR(A1)+1)+(IF(MONTH(A1)<=4,1,0)+(IF(MONTH(D1)>=4,1,0)))

@ErikTyler:

Nice!

Two suggestions:

- multiply your formula by ($D1>=$A$1) to handle the situation when today is, say, 25-Apr-2017, and the retirement date was 15-Apr-2017. Obviously, no more reviews are due.
- get rid of the two IF calls -- just use ...+(MONTH($A$1)<=4)+(MONTH($D1)>=4).

 

[inactiveuserps07]

Tetra201, good catch on those dollar signs ... and eliminating the IFS. Redundant, yes. And also, good thinking on the "already retired" addendum. Teamwork! * smack *


=(YEAR($D1)-(YEAR($A$1)+1)+(MONTH($A$1)<=4)+(MONTH($D1)>=4))*($D1>=$A$1)


And if a specific review date is known and important:



=(YEAR($D1)-(YEAR($A$1)+1)+($A$1<=[specific_date])+($D1>=[specific_date]))*($D1>=$A$1)