Finding the earliest date from a range of cells

[roniaelm]

Hi,

From a range of cells in multiple columns with dates I am trying to

get the earliest date listed in my summary row.
I have managed to do this using the MIN function however I am having
two problems with this, if the users have entered text in these
columns then this function also gives me 00-Jan-00 in the summary
column, I prefer that it does nto give me any value at all as this can
be misleading, or if they provide the date using text such as Early
Jan then the MIN function does not recognise it as a date as you can
see in my eg. below.

Then MIN function I used was MIN(A1:A3) for the first column and so
on.

E.g
A B C
1 2-Jan-07 2-Sep-07 N/
A
2 6-Mar-08 Early Jan N/A
3 24-Dec-07 5-Feb-07 N/A

Summary 2-Jan-07 5-Feb-07 00-Jan-00

I am not sure how to fix this. I appreciate any help!

Ronia

 

[squenson via OfficeKB.com]

You can test the presence of an error by using the function ISERR which
returns TRUE if there is an error. So the formula could be:
=IF(ISERR(A1:A3), "Put here text in case of error or simply two double
quotes",Min(A1:A3))

 

[Guest]

Try this:

=IF(COUNT(A1:A3),MIN(A1:A3),"")

Hi,

get the earliest date listed in my summary row.
I have managed to do this using the MIN function however I am having
two problems with this, if the users have entered text in these
columns then this function also gives me 00-Jan-00 in the summary
column, I prefer that it does nto give me any value at all as this can
be misleading, or if they provide the date using text such as Early
Jan then the MIN function does not recognise it as a date as you can
see in my eg. below.

Then MIN function I used was MIN(A1:A3) for the first column and so
on.

E.g
A B C
1 2-Jan-07 2-Sep-07 N/
A
2 6-Mar-08 Early Jan N/A
3 24-Dec-07 5-Feb-07 N/A

Summary 2-Jan-07 5-Feb-07 00-Jan-00

I am not sure how to fix this. I appreciate any help!

Ronia