Excel条件格式：基于其他单元格的单/多日期高亮日历单元格

问题说明 #

需要给E5:K10区域（日期格式的日历单元格）设置条件格式，根据E16:E30的内容高亮：

• E16:E30包含两种内容：单日期（如8）、日期范围（如8 - 10，对应当月8日-10日）
• 当前仅能实现单日期高亮，日期范围无法触发对应区间的单元格高亮

解决方案 #

核心逻辑 #

判断当前日历单元格的日数（比如2024/8/8提取为8）是否满足以下任一条件：

• 匹配E16:E30中的单日期单元格
• 落在E16:E30中某单元格的日期范围内

条件格式公式 #

选中E5:K10区域，新建条件格式规则，使用以下公式：

=SUMPRODUCT(
    --(
        (TEXT(E5,"d")=$E$16:$E$30) + 
        (ISNUMBER(SEARCH("-",$E$16:$E$30)) * 
         (TEXT(E5,"d")>=LEFT($E$16:$E$30,FIND("-",$E$16:$E$30)-1)) * 
         (TEXT(E5,"d")<=MID($E$16:$E$30,FIND("-",$E$16:$E$30)+2,99))
        )
    )
)>0

公式拆解（大白话解释） #

1. TEXT(E5,"d")：把当前日历单元格的日期转成“日数”文本（比如2024/8/8变成8），和E16:E30的格式对齐
2. TEXT(E5,"d")=$E$16:$E$30：检查这个日数是不是和某个单日期单元格完全匹配
3. ISNUMBER(SEARCH("-",$E$16:$E$30))：判断E16:E30里的单元格是不是日期范围（有没有-）
4. LEFT(...,FIND("-",...)-1)：从日期范围单元格里抠出起始日（比如8 - 10里的8）
5. MID(...,FIND("-",...)+2,99)：从日期范围单元格里抠出结束日（比如8 - 10里的10，99是足够大的数，确保把后面的字符都取到）
6. --(...)：把TRUE/FALSE的判断结果转成1/0，方便用SUMPRODUCT统计符合条件的数量
7. SUMPRODUCT求和后大于0，说明当前单元格符合高亮条件

设置步骤 #

1. 选中E5:K10整个区域
2. 点击菜单栏「开始」→「条件格式」→「新建规则」
3. 选择「使用公式确定要设置格式的单元格」
4. 粘贴上面的公式，设置你想要的高亮格式（比如填充黄色）
5. 点击确定，完成设置

验证效果 #

• 单日期（如E16是8）：E5:K10里所有日数为8的单元格会高亮
• 日期范围（如E17是8 - 10）：E5:K10里日数为8、9、10的单元格都会高亮

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