FInd min Date in a date range with duplicate date-CodePudding

Using Excel and I'd like a formula that will return the earliest date from a row that contain duplicate date. for example: my row has 5 different dates, 1 duplicate and one blank cell

    A          B           C            D            E             F
09/01/2021  08/28/2021  09/08/2021   blank cell   09/01/2021   08/15/2021

I am using formula =MIN((A1:F1)

the return should be "8/28/2021" but I'm getting "1/1/1900" can someone please help? that will be greatly appreciated

CodePudding user response：

Try this:

=MIN(IFERROR(A1:F1*A1:F1/A1:F1,99999))

It will replace all the 0's with 99999 if it cannot be divided by itself (0).

(99999 is actually 10/13/2173 and was chosen at random as a date that will be later than the other dates on your list.)

CodePudding user response：

You can do it with SMALL function:

My formula in column J is =SMALL(A1:G1;1)

Notice my date format is dd/mm/yyyy