使用公式识别复杂excel字段中的最大日期

Question

我有一个excel文件，其中有一个列作为项目分配。该列的示例值如下：

XXX E(10月15日至30日7月16日)、YYY G(10月15日至29日7月16日)、ZZZZ C(181月16日至2312月16日)、AAA B(041月16日至287月16日)

您可以看到，每个任务都有开始日期和结束日期。结束日期可以使用')‘括号，即')’之前的7个字符来标识。

我必须写一个公式，给我确定的所有结束日期的最大值。我知道，我可以使用宏，但是由于安全问题，我们的组织希望避免宏。

上述领域的产出应为2016年12月23日至2016年。

请建议一下。

Answer 1

假定，对于给定的字符串(在A1中)：

1)该字符串中每个结束日期的格式都非常不精确。

( 2)每个结束括号前的条目绝不是结束日期以外的任何其他内容。

然后，数组公式**

=MAX(0+MID(A1,MODE.MULT(IF(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)={")",")"},ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))-7,7))

将包含此公式的单元格设置为您想要的格式，例如自定义类型ddmmmyy。

请注意，如果您使用的Excel版本中的数组常量中的行分隔符和列分隔符分别不是分号和逗号，则该解决方案可能需要进行小的修改。

问候

**数组公式的输入方式与“标准”公式不同。首先按住CTRL和SHIFT，然后按ENTER键，而不是只按ENTER键。如果您做得正确，您会注意到Excel在公式周围放置了花括号{} (尽管不要自己尝试手动插入这些括号)。

Answer 2

不太灵活，但它的工作时间，只要你有4个结束日期，而不偏离给定的格式。除了VBA之外，我想不出任何其他的方法，这将是理想的。

=TEXT(MAX(MID(A1,FIND("-",A1,1)+1,7),MID(A1,FIND("-",A1,FIND("-",A1,1)+1)+1,7),MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,1)+1)+1)+1,7),MID(A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,FIND("-",A1,1)+1)+1)+1)+1,7)),"ddmmmyy")

Answer 3

使用MID提取括号之间的数据。假设您的数据位于A列中，则可以使用公式：

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

您将得到以连字符分隔的开始日期和结束日期(如:10月15日至7月16日)。现在使用LEFT、RIGHT和SEARCH获取开始日期和结束日期：

开始日期：

=LEFT(B2,SEARCH("-",B2)-1)

结束日期：

=RIGHT(B2,LEN(B2)-SEARCH("-",B2))

若要将这些日期转换为文本，请按以下方式使用TEXT：

=TEXT(D2,"ddmmmyy")

希望我已经包括了你的报告可能需要的所有公式。