Excel PAT测试表公式问题求助：日期筛选排序异常

问题核心 #

初始IF条件不满足时返回的-1会被Excel识别为1900年1月1日之前的极早日期，导致不符合条件的行排在排序结果最前端；返回空值则会让列混合日期与文本类型，触发非预期的文本排序逻辑。

解决方案 #

1. 替换返回值为极晚日期 #

将公式末尾的-1替换为DATE(9999,12,31)（Excel支持的最大有效日期之一），这样不符合条件的行在“最早到最晚”排序时会自动排在所有正常到期日期之后，同时整列保持纯日期类型，确保排序逻辑正常。

修改后的完整公式：

=IF(AND(A5<>"",B5<>"",UPPER(C5)="YES"),
LET(
  latest_test, SORT(FILTER(TABLE_PAT_TESTING, TABLE_PAT_TESTING[APP ID]=A5), 2, -1),
  IF(ROWS(latest_test)>0,
    LET(
      test_date, INDEX(latest_test,1,2),
      interval, INDEX(latest_test,1,10),
      test_date + IF(interval<>0, interval, SETTINGS_PAT_TEST_DEFAULT)
    ),
    SETTINGS_SYMBOLS_WARN
  ),
  DATE(9999,12,31)
)

2. 隐藏极晚日期的显示（可选） #

如果不想让同事看到9999/12/31这个占位日期，可以通过条件格式隐藏它：

• 选中到期日期列的所有单元格
• 打开「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
• 输入公式（假设到期日期在D列）：=D5=DATE(9999,12,31)
• 设置格式：选择「字体颜色」为白色，或在「数字」选项卡设置自定义格式为;;（三个分号，会隐藏单元格内容但保留实际值，不影响排序）

额外优化说明 #

原公式中多次使用CHOOSEROWS和CHOOSECOLS可以简化为INDEX函数，让公式更简洁易读，同时保持功能完全一致。

内容的提问来源于stack exchange，提问作者Oliver Jones