Excel 2010 countif cells dates are less than 90 days of other cells dates

[k1ghtt3mplar]

Hello, i am hoping someone can help with a few problems?
I have dates spanning from 2014-2017 in Column A (A6:A400) and dates in Column B (B6:B400), I require a formula that will compare dates in A6 against B6 and so on to finally A400 against B400 where there are less than 90 days between dates of columns/cells.

Can this be done?

If this problem is solved and in addition if possible using the same formula to the question above in addition I then need to be break down in to rolling periods e.g. i have a cell that records today (11 sep 17 - 11 sep 16) and so on back to 2014.
This will hopefully populate a data base within the 3 columns that continue to update accordingly, of how many things have been achieved in less than 90 days from Sep 2014 to 2017.

Thankyou in advance if anyone can help.

 

[Veritan]

Hello, if you're willing to use a helper column, this is fairly simple. In column C, I put the formula =IF(B6-A6<90,"Less than 90 days","Over 90 days"). Then in another cell I put the formula =COUNTIF(C6:C400,"Less than 90 days"). This counts what you need for your first question. The text in those formulas can be changed to anything as long as the change is the same in both formulas.

Could you provide some clarification on your 2nd question? I'm not quite sure what you mean.

 

[Aladin Akyurek]

Is this what you need?

In C6 enter and copy down:

=(B6-A6 < 90)+0

A hit (difference is less than90 days) is marked as 1, a miss as a 0.

 

[k1ghtt3mplar]

Hello, if you're willing to use a helper column, this is fairly simple. In column C, I put the formula =IF(B6-A6<90,"Less than 90 days","Over 90 days"). Then in another cell I put the formula =COUNTIF(C6:C400,"Less than 90 days"). This counts what you need for your first question. The text in those formulas can be changed to anything as long as the change is the same in both formulas.

Could you provide some clarification on your 2nd question? I'm not quite sure what you mean.

Thankyou it is appreciated. I have placed another column C as suggested and it works a dream.

Clarification to the second question. I wish to use the information in column c "less than 90 days" newly created, to cross reference with dates in column A.
This is to count in columns D (2014), E (2015) etc to G if less than 90 days and in year respectively. I hope this helps the explanation.

 

[Aladin Akyurek]

Thankyou it is appreciated. I have placed another column C as suggested and it works a dream.

Clarification to the second question. I wish to use the information in column c "less than 90 days" newly created, to cross reference with dates in column A.
This is to count in columns D (2014), E (2015) etc to G if less than 90 days and in year respectively. I hope this helps the explanation.

Is this what you need?

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(1/(B2:B400*A2:A400)),IF(B2:B400-A2:A100 < 90,1)))

This does not test whether the dates in A:B are of a particular year.