Excel条件判断公式求助：按星期判断时长合规性

需求说明 #

需要在C列生成判断结果，规则如下：

• 变更结束于周日，且结束时间与关闭时间的间隔＞48小时 → 显示 Fail
• 变更结束于周五/周六，且间隔＞72小时 → 显示 Fail
• 变更结束于周一至周四，且间隔＞24小时 → 显示 Fail
• 其他所有情况 → 显示 Pass

现有公式问题分析 #

1. 第一个公式=IF(AA2<=TIME(72,0,0),(AND(AH2="Friday","pass","fail")))

   • 语法错误：AND函数参数使用错误，且TIME函数仅支持0-23小时范围，无法表示72小时
   • 逻辑完全不符合需求，仅判断了周五和72小时的单一情况

2. 第二个公式=IF(AND(AH3="Sunday", AA3<"48"), "Fail", "Pass")

   • 类型错误：将数值间隔与文本"48"比较，会导致计算异常
   • 覆盖不全：仅处理了周日的情况，未包含其他星期的判断规则

3. 嵌套公式逻辑方向正确，但存在冗余：

   • 多层IF嵌套可读性差，维护困难
   • 依赖手动提取的星期文本，若文本存在拼写/大小写/空格问题，会导致判断失效

优化后的公式方案 #

推荐使用WEEKDAY函数直接从结束时间提取星期数，避免文本依赖，同时用OR+AND简化逻辑：

方案1：直接从结束时间判断星期（最可靠）

假设变更结束时间在A列，关闭时间在B列，公式输入到C2单元格：

=IF(OR(
    AND(WEEKDAY(A2,2)=7, (A2-B2)*24>48),
    AND(WEEKDAY(A2,2)>=5, (A2-B2)*24>72),
    AND(WEEKDAY(A2,2)<=4, (A2-B2)*24>24)
), "Fail", "Pass")

• 解释：
  • WEEKDAY(A2,2)：返回1=周一，2=周二，…，6=周六，7=周日
  • (A2-B2)*24：计算结束时间与关闭时间的间隔小时数
  • 任意一个AND条件满足时，返回Fail，否则返回Pass

方案2：使用已提取的星期文本

如果已将星期提取到D列（中文/英文），可使用以下公式：

• 中文星期版本：

=IF(OR(
    AND(D2="周日", (A2-B2)*24>48),
    AND(OR(D2="周五",D2="周六"), (A2-B2)*24>72),
    AND(OR(D2="周一",D2="周二",D2="周三",D2="周四"), (A2-B2)*24>24)
), "Fail", "Pass")

• 英文星期版本：

=IF(OR(
    AND(D2="Sunday", (A2-B2)*24>48),
    AND(OR(D2="Friday",D2="Saturday"), (A2-B2)*24>72),
    AND(OR(D2="Monday",D2="Tuesday",D2="Wednesday",D2="Thursday"), (A2-B2)*24>24)
), "Fail", "Pass")

注意事项 #

• 确保A列和B列是日期时间格式，而非文本格式，否则间隔计算会出错
• 若关闭时间早于结束时间（间隔为负数），公式会自动返回Pass，符合逻辑
• 优先选择方案1，避免因星期文本的格式/拼写错误导致判断失效

内容的提问来源于stack exchange，提问作者Kirstie H Hunter