Reset count after 6 months from original date

[mmhowa3]

Hello all!

I am making an attendance tracker that counts the occurrences (entered by date) and would like the "current count" to reset every 6 months, starting with the first entry in cell A11. Below is the formula I am using in cell B7 - however I would like the count to reset 6 months after 1/1/18. So, the current count would read 3, even though there are 4 entries.
Any help or suggestion to make this easier would be greatly appreciated! Thank you!


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=COUNTIF(A11:A72,"<"&EDATE(TODAY(),6))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]current count-->[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD]1/1/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]2/1/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]3/1/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]11/1/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cerfani]

however I would like the count to reset 6 months after 1/1/18

that's not what your formula says, your formula counts occurrences of dates that are before the same date, 6 months in the future.

use countifs to check two criteria... the date should be less than or equal to Today() and greater than EDATE(TODAY(),-6) ... assuming you are trying to count occurences of dates in the past 6 months. You can check the future 6 months by flipping some signs

 

[mmhowa3]

Thanks - I am new to these functions.
I tried using: =COUNTIFS(A11:A72,">="A11,A11:A72,">"EDATE(TODAY(),-6))
but it isn't correct. Is it the 'criteria 1' part that I am messing up?
Please help

 

[cerfani]

Well if in fact if you are trying to calculate relative to the date value in A11 then TODAY() is completely irrelevant. You are either checking relative to A11 or TODAY ... your formula is referencing both. I still don't really understand what you are trying to do but if I had to guess I would say you should replace TODAY() with A11... then you are counting dates relative to A11. So if a date in the column is within a certain range from A11 then it will or will not be counted.

The function you wrote in english says, count any dates after that occur on or after the date in A11... AND ... any dates that are after the same date from today, 6 months ago.

So you can see you are checking each date relative to two different dates. I don't think you meant that but rather it all to be relative to A11.

I think it helps to just say it in english rather than in excel terms.

 

[cerfani]

Are you trying to count dates that fall between July 1, 2017 and Jan 1, 2018 given that A11 is Jan 1 2018? Is it all dependent on A11?

 

[mmhowa3]

I see - I am trying to relate to whatever date is in cell A11.
Let me try to explain again without my formula:
I will be tracking attendance occurrences within a 6 month period. The beginning of that period will be the first date listed (cell A11).
I would like to count the total number of occurrences, but I also need this count to reset after 6 months from the initial date.
Which formula should I use?

 

[cerfani]

Ok so you are trying to count occurrences of dates between Jan 1, 2018 and 6 months later July 1, 2018 WHEN A11 is Jan 1 2018... so between A11 and EDATE(A11,6)

so...

=COUNTIFS(A11:A72,">"&A11,A11:A72,"<="&EDATE(A11,6))

(if it needs to include A11 then change to ">="&A11)

 

[mmhowa3]

That worked great! Thanks!
Is there a way for this count to reset automatically after 6 months from the date in column A11?
For example, if I have 7 entries between Jan and June (the formula provided produces '7'), then I add another one in August, can the count start again at 1?

 

[cerfani]

Well maybe there is a way to do it in a single formula but it would be an ugly formula, maybe Aladin will pop in and give you something... but here is the way i would do it...

 

[mmhowa3]

I think that will work - thank you again for your help