QUESTION: I have a date in A1. Also a list of Dates in Col.E
1. How to find dates older than 10 Years, 5 to 10 years old and less than 5 years from the date in A1?
2. How to count No. of dates that fall between two dates, Say dates between 31/3/2009 and 15/6/2010?
ANSWER: Nabam,
Not exactly sure what you mean by FIND, but one answer is to 1. Apply an autofilter with the specific critieria you list
or put a formula in a dummy column such as starting in cell F1 =IF(E1<DATE(YEAR($A$1)-10,MONTH($A$1),DAY($A$1)),"Older than 10 years",IF(E1<DATE(YEAR($A$1)-5,MONTH($A$1),DAY($A$1)),"Between 10 and 5 years",IF(E1<$A$1,"Less than 5 years","Not Before "&TEXT($A$1,"dd/mm/yyyy"))))
You might want to use <= rather than < depending on how you want to treat the exact match situation (exactly 5 years).
2. =Countif(E:E,">=31/3/2009")-Countif(E:E,">15/6/2010")
So you count all those dates greater than or equal to 31/3/2009 and then subtract the count of all dates greater than 15/6/2010 and that should be the count of the dates between those two dates (inclusive).
---------- FOLLOW-UP ----------
QUESTION: Thanks Tom. Sorry for making confusion. Actually I want to count the no. of dates listed in col.E for dates older than 10 Years, 5 to 10 years old and less than 5 years from the date in A1?
Answers for question no.2 is ok.
Nabam,
count for more than 10 years
=COUNTIF(E:E,"<"&DATE(YEAR(A1)-10,MONTH(A1),DAY(A1)))
count for between 5 and 10 years
=COUNTIF(E:E,"<="&DATE(YEAR(A1)-5,MONTH(A1),DAY(A1)))-COUNTIF(E:E,"<"&DATE(YEAR(A1)-10,MONTH(A1),DAY(A1)))
count for less than 5 years from the date in A1 but not after the date in A1
=COUNTIF(E:E,"<="&DATE(YEAR(A1),MONTH(A1),DAY(A1)))-COUNTIF(E:E,"<"&DATE(YEAR(A1)-5,MONTH(A1),DAY(A1)))
if you don't want to exclude dates after the date in A1
=Count(E:E)-COUNTIF(E:E,"<"&DATE(YEAR(A1)-5,MONTH(A1),DAY(A1)))
Advertisement