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

k1ghtt3mplar

New Member
Joined
Sep 12, 2017
Messages
3
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top