Finding the earliest date from a range of cells

R

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
 
S

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

Teethless mama

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top