Excel日期条件格式设置：实现员工当周/上周新冠检测列自动标绿

前置范围对齐 #

配置前先对齐表格基础结构，所有公式里的引用范围需要匹配实际表格调整：

• 表头固定在第1行，员工数据从第2行开始逐行存储
• 每一行内，该员工所有新冠检测日期的连续列范围为举例值G列到AP列（实际使用时替换为表中存放所有检测记录的列范围）
• 「Tested this week」、「Tested last week」两列分别为固定标记列，假设对应表中的AB列、AC列

「Tested this week」列绿色标记规则配置 #

• 选中该列所有需要应用规则的单元格（例如AB2到AB1000，覆盖全部员工行）
• 打开条件格式面板，选择「新建规则」-「使用公式确定要设置格式的单元格」
• 在公式输入框中填入如下公式，根据实际检测列范围调整$G2:$AP2部分：
  =SUMPRODUCT((($G2:$AP2<>"")*($G2:$AP2>=TODAY()-WEEKDAY(TODAY(),2)+1)*($G2:$AP2<=TODAY()-WEEKDAY(TODAY(),2)+7))>0)
• 点击「格式」按钮，将单元格填充色设置为绿色，保存规则即可。
  公式逻辑：逐行扫描当前行的所有检测日期，跳过空单元格，只要存在任意一条日期落在当前自然周（周一到周日的完整周范围），就触发绿色标记。公式直接通过日期边界计算当周范围，不受跨年、年份周期的影响。

「Tested last week」列绿色标记规则配置 #

• 同样选中该列所有需要应用规则的单元格（例如AC2到AC1000，覆盖全部员工行）
• 新建同类型的公式驱动条件格式规则
• 在公式输入框中填入如下公式，同样根据实际检测列范围调整$G2:$AP2部分：
  =SUMPRODUCT((($G2:$AP2<>"")*($G2:$AP2>=TODAY()-(WEEKDAY(TODAY(),2)+6))*($G2:$AP2<=TODAY()-WEEKDAY(TODAY(),2)))>0)
• 设置同样的绿色填充格式，保存规则即可。
  公式逻辑：逐行扫描当前行的所有检测日期，只要存在任意一条日期落在上一个自然周（上周一到上周日的完整周范围），就触发绿色标记。同样采用日期边界计算方式，在新年第一周时也能准确匹配上一年最后一周的记录，不会出现判断失效的问题。

配置避坑提示 #

• 引用格式要求：检测区域的列号必须加绝对引用符号$（例如写$G2而非G2），行号不能加$，这样规则应用到每一行时，会自动匹配当前行的检测数据，不会出现整列错位匹配第一行数据的问题。
• 周起始调整：如果团队以周日为每周起始，只需要把公式中WEEKDAY函数的第二参数从2改成1即可，其余部分不需要调整。
• 误判规避：公式中的空值判断$G2:$AP2<>""，会自动跳过未录入检测记录的空单元格，不会把空值识别为1900年的异常日期触发误标。
• 实时更新：公式中的TODAY()函数会自动跟随系统当天日期更新，不需要每周手动调整规则，每次打开表格都会自动匹配当前周、上一周的检测记录更新标记状态。

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