Excel培训矩阵：按到期日期时段设置单元格条件格式求助

需求说明 #

• 搭建员工培训矩阵，记录员工所有培训的完成日期、到期续期日期，培训自完成日起每3年到期
• 单元格格式规则：
  • 培训已过期（距完成日超3年）：单元格变红
  • 培训2个月内到期：单元格变琥珀色
  • 培训距到期超2个月：单元格变绿色

已尝试的无效方案 #

VBA代码 #

Sub CheckDateAndFormat()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("C5:C20")
    For Each cell In rng
        If cell.Value >= DateAdd("yyyy", -3, Date) And cell.Value <= DateAdd("yyyy", 3, Date) Then
            cell.Interior.ColorIndex = 36
        End If
    Next
End Sub

问题：逻辑错误，仅判断了日期在过去3年到未来3年的范围，未区分过期、临近到期和正常三种状态

条件格式公式 #

=AND(C6>TODAY(), C6-TODAY()<=1095)

问题：仅能标记未来3年内的日期为绿色，无法处理过期或临近到期的变色需求

推荐解决方案（条件格式，无需VBA） #

假设表格中C列为完成日期，D列为到期续期日期，提供两种设置方式：

基于「到期续期日期」设置条件格式（推荐，更直观） #

选中到期日期列（如D5:D20），依次添加3条条件格式规则（规则从上到下优先级递减）：

1. 红色（已过期）
   • 规则类型：使用公式确定要设置格式的单元格
   • 公式：=D5<TODAY()
   • 格式：填充色选红色
2. 琥珀色（2个月内到期）
   • 公式：=AND(D5>=TODAY(), D5<=EDATE(TODAY(),2))
   • 格式：填充色选琥珀色
3. 绿色（距到期超2个月）
   • 公式：=D5>EDATE(TODAY(),2)
   • 格式：填充色选绿色

基于「完成日期」计算到期时间设置条件格式 #

选中完成日期列（如C5:C20），添加3条条件格式规则：

1. 红色（已过期）
   • 公式：=EDATE(C5,36)<TODAY()
   • 格式：填充色红色
2. 琥珀色（2个月内到期）
   • 公式：=AND(EDATE(C5,36)>=TODAY(), EDATE(C5,36)<=EDATE(TODAY(),2))
   • 格式：填充色琥珀色
3. 绿色（距到期超2个月）
   • 公式：=EDATE(C5,36)>EDATE(TODAY(),2)
   • 格式：填充色绿色

修正后的VBA方案 #

如果你更倾向用VBA实现，以下代码可正确判断三种状态：

Sub CheckTrainingExpiry()
    Dim rng As Range
    Dim cell As Range
    Dim expiryDate As Date
    
    ' 替换为你实际要处理的完成日期列范围
    Set rng = Range("C5:C20")
    
    For Each cell In rng
        ' 跳过非日期单元格
        If IsDate(cell.Value) Then
            expiryDate = DateAdd("yyyy", 3, cell.Value)
            
            Select Case True
                ' 已过期：红色（ColorIndex 3）
                Case expiryDate < Date
                    cell.Interior.ColorIndex = 3
                ' 2个月内到期：琥珀色（ColorIndex 36）
                Case expiryDate <= DateAdd("m", 2, Date)
                    cell.Interior.ColorIndex = 36
                ' 距到期超2个月：绿色（ColorIndex 4）
                Case Else
                    cell.Interior.ColorIndex = 4
            End Select
        Else
            ' 空单元格清除填充色
            cell.Interior.ColorIndex = xlColorIndexNone
        End If
    Next cell
End Sub

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