Max Value in Year

[Jan Kronsell]

I have a column containing dates, (European date format)

01-05-2006
07-09-2006
01-12-2007
07-12-2007
31-12-2007
01-01-2008
03-02-2008
....
....
Date are not always sorted.

How can I find the max date in 2006, 2007 2008 and so on?

In 2006 that would be 07-09-2006, in 2007 31-12-2007

Jan

 

[Gary''s Student]

Excellent question! Try this array formula:

=MAX(IF(YEAR(A1:A7=2007),A1:A7))

insert this with CNTRL-SHIFT-ENTER rather than just the ENTER key

 

[Ron Coderre]

Try this:

=MAX(INDEX((YEAR(A1:A60)=2007)*A1:A60,0))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jan Kronsell]

Thank you very much to both of you.

Jan

Try this:

=MAX(INDEX((YEAR(A1:A60)=2007)*A1:A60,0))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)