finding correct minimum date in a range specified

问题:

This question already has an answer here:

returning empty cell if minimum of a range contains all empty values (1 answer)

Closed 2 years ago.

I have a following formula which finds the minimum date in a dynamic range which I specified in my formula

=MIN(INDIRECT(ADDRESS(ROW(AF4);COLUMN(AF4))&":af"(MIN(IF(A4:A108="";ROW(A4:A108))))))

In the range specified in my formula, there can be also value "N/A" and I want to update my formula with if clauses which will return empty string ("")if there is a value "N/A". If there is a date inside then formula should return a date which already does as above version.

Reason why i need it is in my project timeline some dates are not specified so i left them as N/A. When formula searches for dates in the dynamic range and does not find any date inside this range, it writes automatically 00.01.1900 as a minimum value and i want to avoid this.

Can anyone help me to adjust my formula?

EDIT I have a dynamic range in AF which contains START date of different activities which are listed in cell AE. My current formula searches in this dynamic range which activity starts first with MIN Function and returns this date. However, if all activity start dates in that range are not known, then these activities have a value of "N/A" instead of date. And Excel Min function returns automatically 00.01.1900 but i want it to return blank if all cells in this range are "N/A" because 00.01.1900 is not a correct date.

回答1:

00/01/1900 in excel has the value of 0, you can see this by typing this date into a number formatted cell.

If you simply equate the result of your formula against one then you can just return a blank cell instead:

=IF([YOUR FORMULA]=0,"",[YOUR FORMULA])

Or if you know it will return 00.01.1900 every time this occurs then you could substitute that text out of the cell:

=SUBSTITUTE([YOUR FORMULA],"00.01.1900","")