Counting of dates

QuestionEdit

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.

AnswerEdit

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)))