Max Value in Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan Kronsell - 08 Feb 2008 15:20 GMT

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

Reply to this Message

Gary''s Student - 08 Feb 2008 15:31 GMT

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

Signature

Gary''s Student - gsnu2007d

> I have a column containing dates, (European date format)
>
[quoted text clipped - 14 lines]
>
> Jan

Reply to this Message

Ron Coderre - 08 Feb 2008 15:35 GMT

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)

>I have a column containing dates, (European date format)
>
[quoted text clipped - 14 lines]
>
> Jan

Reply to this Message

Jan Kronsell - 08 Feb 2008 15:40 GMT

Thank you very much to both of you.

Jan

> Try this:
>
[quoted text clipped - 27 lines]
>>
>> Jan

Reply to this Message