Finding a maximum/minimum date in 2006
 

Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre

Finding a maximum/minimum date in 2006
 

=MAX(A1:A4*(A1:A4<DATE(2007,1,1))) entered as an array formula (Control
Shift Enter)
--
David Biddulph

"megacata" wrote in message
ups.com...
Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre

Finding a maximum/minimum date in 2006
 

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(IF(YEAR(A1:A4)=2006,A1:A4))
=MIN(IF(YEAR(A1:A4)=2006,A1:A4))

Format as DATE

Biff

"megacata" wrote in message
ups.com...
Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre

Finding a maximum/minimum date in 2006
 

Thanks a lot for your help David, it works great!

Pierre
David Biddulph wrote:
=MAX(A1:A4*(A1:A4<DATE(2007,1,1))) entered as an array formula (Control
Shift Enter)
--
David Biddulph

"megacata" wrote in message
ups.com...
Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre

Finding a maximum/minimum date in 2006
 

It works great too...
Thanks Biff!
T. Valko a écrit :

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MAX(IF(YEAR(A1:A4)=2006,A1:A4))
=MIN(IF(YEAR(A1:A4)=2006,A1:A4))

Format as DATE

Biff

"megacata" wrote in message
ups.com...
Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre

Finding a maximum/minimum date in 2006
 

You're welcome. Thanks for the feedback!

Biff

"megacata" wrote in message
ups.com...
It works great too...
Thanks Biff!
T. Valko a écrit :

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=MAX(IF(YEAR(A1:A4)=2006,A1:A4))
=MIN(IF(YEAR(A1:A4)=2006,A1:A4))

Format as DATE

Biff

"megacata" wrote in message
ups.com...
Hello all,

I have a list of dates mixing years
01/05/06
01/01/07
02/05/06
03/03/07
etc..

How can I create a formula that will find out the latest year in 2006
(excluding 2007)!
It's is not a list in a database, so I don't want to use filtering or
extracting!

Thanks a lot for your help

Pierre