Microsoft Excel - findin the min date in excel

hi

i have been trying to find the min date in excel

however when the dates pull thou from the sheets to the total sheet there is a error!

when there is no date to pull thou the date on the front sheet defaults to 0-jan-00.

so when you want to find the min of say 5 cells eg:-

cell 1) 05-jan-10

cell 2) 06-jan-10

cell 3, cell 4 and cell5 all default to 0-jan-00 then excel thinks the min date is 0-jan-00

i need excel to pull thou the 05-jan-10

can anyone help!!!

If our five cells are in the range A1:A5:

=SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

When I copied your cell values onto a sheet in Excel 2007, it interpreted 0-Jan-00 as text rather than a date. When I tried the formula =MIN(A1:A5) I got the result as 05-Jan-10

Format an empty cell as a Date of Type 14-Mar-01.  Now enter the simplest formula referencing an empty cell into that cell, e.g. =A2.  With A2 empty, the formula evaluates to zero.  As the zero is fomatted as a date, it displays 0-Jan-00, but the underlying value is not text, it's the number zero (the formulaic value of an empty cell).

Another, and perhaps more apposite, solution would be to use a more complex formula for the "dates that pull through from another sheet," e.g., instead of a formula like this for those cells:

=Sheet3!A2

use a formula like this:

=IF(ISBLANK(Sheet3!A2),"",Sheet3!A2)

Then the referencing range would show blank cells for the blank dates on the referenced range and a simple MIN (as you suggest) would work with the referencing range.  (The MIN function ignores any empty cells, logical values, or text values; the MINA function does not.)

thanks that worked a treat

louise