在公式中指定的范围内查找正确的最小日期，该范围可能仅包含字符串值 [英] finding correct minimum date in a range specified in formula which may contain just string values

问题描述

我有一个以下公式，可以在我在公式中指定的动态范围内找到最短日期

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))))))

在公式中指定的范围内，也可以有值"N/A"，我想用if子句更新公式，如果有值"N/A"，则该子句将返回空字符串(") .如果里面有一个日期，则公式应返回一个已经与上述版本相同的日期.

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.

原因为什么我需要它在我的项目时间轴中，但未指定某些日期，因此我将其保留为N/A.当公式在动态范围内搜索日期并且在该范围内找不到任何日期时，它会自动将00.01.1900写入最小值，我想避免这种情况.

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?

编辑，我在AF中有一个动态范围，其中包含单元格AE中列出的不同活动的开始日期.我当前的公式在此动态范围内搜索，该活动首先从MIN Function开始并返回该日期.但是，如果不知道该范围内的所有活动开始日期，则这些活动的值为"N/A"而不是日期. Excel Min函数会自动返回 00.01.1900 ，但是如果此范围内的所有单元格均为"N/A"，我希望它返回空白，因为00.01.1900不是正确的日期.

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.

推荐答案

excel中的00/01/1900的值为0，您可以通过在数字格式的单元格中键入此日期来看到此值.

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

如果仅将公式的结果与1相等，则可以只返回一个空白单元格:

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])

或者，如果您知道每次发生此操作都将返回00.01.1900，则可以将其替换为单元格之外的文本:

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","")

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

这篇关于在公式中指定的范围内查找正确的最小日期，该范围可能仅包含字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！