Select latest of 3 dates and skip blank cells

Hello all.

First post on the forum after trying for some time to solve a problem which I believe someone will be able to solve quite easily on here.

I am trying to find the latest of 3 dates in 3 seperate columns and display it in a seperate column. I have used the Max function which seems to work but my sheet will have one or two of the columns blank on occasions. Currently it is showing 00/01/00 using the max formula. I need to skip the blanks somehow.

I think that MAX should work. If you use the formula

=MAX(H17:J17)

what do you get?

That should only return 00/01/00 (which is zero formatted as a date) if all 3 cells are blank or if there are dates in the cells which are text-formatted and not recognised as dates.

To avoid getting 00/01/00 when all 3 cells are blank use a custom format of

dd/mm/yy;;

Note the 2 semi-colons.....

If the above doesn't work then I suggest that your dates aren't true dates (they are text), what do you get with

=COUNT(H17:J17)

What do the dates look like, where do they come from, are they generated by formulas or something else?

I'm sure I can if you give me some instuction on how to do this

If I open your file and put this formula in F3

=COUNT(A3:C3)

Then the result is 0......this means your "dates" are not real dates but text (otherwise they would be numbers and COUNT counts numbers).

Try converting to true dates like this:

Put a zero in any blank cell and copy that cell.
Select columns A to C
Edit > Paste Special > under "operation" select "Add" > OK > ESC. Now you have converted to dates and blank cells will show zero. Format as

mm/dd/yy

....to hide zeroes and get consistent date formats

Now use

=MAX(A3:C3) in F3 copied down

format column F the same way

I got the formatting the cells to mm/dd/yy step. I am using the UK version of dd/mm/yy will that make a difference?


I formatted the entire range using the standard date formatting and not custom.


The blank cells are showing 00/01/00??

Also if I now try the =COUNT(A3:C3) again is still comes up as zero

You need to do this part - that's crucial - I meant dd/mm/yy......

Originally Posted by daddylonglegs

Put a zero in any blank cell and copy that cell.
Select columns A to C
Edit > Paste Special > under "operation" select "Add" > OK > ESC. Now you have converted to dates and blank cells will show zero. Format as

dd/mm/yy

OK, sorry, you still need to custom format with the version I suggested earlier with two colons at the end, e.g.

dd/mm/yy;;

that will suppress the zeroes

MAX formula should work, though - with or without that formatting step......