Calculating lowest date from two cells

Hi guys -

I have two cells with dates, lets say A1 and B1, and I want to put the lowest (earliest) date in cell C1, however, there are several conditions:

IF both cells are empty, return NO DATES
=IF(AND(A1="",B1=""), "No Date")

IF B1 is blank, and A1 is not blank, return A1
=IF(AND(B1="", A1<>""), A1)

IF A1 is blank and B1 is not blank, return B1
=IF(AND(A1="", B1<>""), B1)

IF boths cells are not blank, and A1 is lower than B1, return A1
=IF(AND(B1<>"", A1<>"", A1<B1), A1)

IF boths cells are not blank, and B1 is lower than A1, return B1
=IF(AND(A1<>"", B1<>"", B1<A1), B1)

IF both cells are not blank, and A1=B1, return A1
=IF(AND(A1<>"", B1<>"", A1=B1), A1)

I can think of the individual formulas, but any more and my brain melts. I

put them all in separate columns, so I get loads of FALSES, and return the column with a date using MIN, but this is a crap way of doing it, it takes up loads of space, and if both dates are blank the MIN function gives me 1/0/1900.

Whats the best way of doing this please?

THANKS!!!!

Presumably the cells either hold valid dates or are blank (or at least non-blank non-dates should be ignored)

Please Login or Register  to view this content.

@Cutter, much better... time for bed