Excel条件格式逾期日期高亮异常求助：公式未正确标记逾期天数

问题分析 #

你的原公式=AND($D6<$F6-(WEEKDAY($F6,2)+1),K$4>=$F6)存在两个核心问题：

1. 日期计算逻辑错误：$F6-(WEEKDAY($F6,2)+1)的计算结果完全偏离逾期截止逻辑（比如目标日期为周一的话，会得到上周六的日期），导致第一个判断条件失效。
2. 高亮范围判断错误：K$4>=$F6会选中所有目标日期及之后的单元格，没有结合「任务已逾期」的核心条件，最终误高亮了所有到期日后的日期。

修正方案 #

根据逾期天数的常规判断逻辑（任务未完成/完成时间晚于截止日，且当前列日期处于逾期区间），提供两种适配场景的修正公式：

情况1：不区分工作日/周末 #

如果逾期判断不需要排除周末，直接使用以下公式：

=AND(OR($D6="", $D6>$F6), K$4>$F6)

• 逻辑说明：
  • OR($D6="", $D6>$F6)：判断任务未完成（实际日期为空）或完成时间晚于目标日期，即任务已逾期。
  • K$4>$F6：判断当前列的表头日期在目标日期之后，即该日期属于逾期天数范围。

情况2：仅考虑工作日（排除周末） #

如果需要将目标日期调整为最近的工作日（比如周末的目标日期自动顺延到周五），使用WORKDAY.INTL函数修正截止日：

=AND(OR($D6="", $D6>WORKDAY.INTL($F6, -1, "0000011")), K$4>WORKDAY.INTL($F6, -1, "0000011"))

• 逻辑说明：
  • WORKDAY.INTL($F6, -1, "0000011")：计算目标日期之前的最后一个工作日（周六、周日为休息日）。
  • 其余逻辑同情况1，仅将原目标日期替换为调整后的工作日截止日。

关键设置提示 #

设置条件格式时需确认：

• 应用范围为你需要高亮的日期数据区域（如K6:Z100）。
• 引用格式正确：$D6/$F6锁定行（确保每行对应自己的任务日期），K$4锁定列（确保每列对应自己的表头日期）。

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