COUNT/COUNTIF function with date calculation

[Jayana]

Good Morning All,

I'm trying to count cells if a calculated value of two dates meets my criteria.
For example I have two columns of dates (Date2 and Date1). I want to count if
Date2 - Date1 = less than 1 year, between 1 and 5 years, between 5 and 10 years, ...
I can create a third column for Date2-Date1 and use countif to count it but I don't want to do that.

Date2 Date1
1/01/2012 1/01/2011
1/02/2011 30/05/2008
5/04/2012 1/09/2005

Any help will be appreciated.


Cheers,

Jayana

[benishiryo]

hi Jayana, so u wanna have 3 formulas? 1 for each criteria separately?
Less than 1 year:
1 to 5 years:
5-10 years:

u can try this for "Less than 1 year":
=SUMPRODUCT(1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"m")<12))

For 1 to 5 years, it overlaps with 5-10 years. so what if u have 1 group that is 5 years? which should it be categorised under?

[Jayana]

Hi benishiryo

Thank for your help. You are absolutely right, I need three formulas in three separate cells and I thing the following criteria should
address the overlapping issue:

Less than 1
1 to less than 5
5 to less than 10
10 and over.

[benishiryo]

try these formulas:

Less than 1:
=SUMPRODUCT(1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"m")<12))
1 to less than 5:
=SUMPRODUCT(1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"y")<=4)*1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"y")>0))
5 to less than 10 :
=SUMPRODUCT(1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"y")>=5)*1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"y")<=9))
10 and over:
=SUMPRODUCT(1*(DATEDIF($B$2:$B$4,$A$2:$A$4,"y")>=10))

let me know if it works

[Jayana]

Many thanks benishiryo.
It works perfectly well.

Jayana