Find minimum date in row with multiple data types in Excel

Question

I am trying to find the minimum date in a row that has both dates and number data types. I tried to use the MINIFS function but that still produces the number value as the minimum, rather than the date. I am restricted to doing this as an in-cell function, rather than in VBA. Also, the data structure is a bit wonky, I know, but it can't be changed.

=MINIFS(A2:C2,A2:C2,CELL("format",A2:C2)="D4")

When I enter =MIN(IF(A6:C6>40000,A6:C6)) I get a #Value error. Excel doesn't like the range before the > symbol.

Answer 1

Remember that dates in Excel are calculated as number of days starting from January 1st, 1900. Thus, if you look at your dates as numbers, you will see that it's in fact a number '44042'. Knowing that, you can use MIN together with IF formula:

=MIN(IF(B5:D5>43831,B5:D5))

Where your suggested numbers are in cells B5:D5 and '43831' is 2020/01/01. Of course, if you have numbers with high values, this solution should be adapted. Here's a screenshot I made: img