☰

Excel 条件格式周末/假期之前的日期

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
Excel 条件格式周末/假期之前的日期

我正在尝试创建一个工资日历。我们每月领两次工资，分别是每月 15 日和最后一天，但绝不会在周末或耶稣受难日领工资。如果 15 日或最后一天是周末，发薪日将推迟到前一个星期五。如果 15 日或最后一天是耶稣受难日，发薪日将推迟到前一个星期四。

如何编写条件格式函数来将发薪日单元格变为红色，并在整个年份都这样做？数据集是一张工作表上的全年日历的日期。我使用的是 Excel 2016。

答案1

发薪日可以是以下八种可能的日子之一：

  • 12 日（如果 13 日是耶稣受难日，那么 13 日、14 日和 15 日就构成了三天的周末）
  • 13号（如果是该周的最后一个工作日*）
  • 14 日（如果是该周的最后一个工作日*）
  • 15日（若为工作日*）
  • 该月的倒数第四天（如果倒数第三天是耶稣受难日，那么该月的最后三天就形成了一个为期三天的周末）
  • 该月的倒数第三天（如果该天是该周的最后一个工作日*）
  • 该月的倒数第二天（如果该天是该周的最后一个工作日*）
  • 该月的最后一天（如果是工作日*）

__________
*工作日为周一至周五 除了耶稣受难日。

在 Excel 2013 中， 如果WEEKDAY(date, 2)date是星期一，…，7，如果date是星期日。这表示这是最近的扩展；如果它不适合您，请说出来，我们可以解决这个问题。使用该WEEKDAY函数以及EOMONTH告诉我们一个月的最后一天的函数，我们可以将上述列表转换为以下公式：

=AND(OR(AND(DAY(A1)=12,A1+1=F$1), AND(DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(DAY(A1)=15,WEEKDAY(A1,2)<=5), AND(EOMONTH(A1,0)-A1=3,A1+1=F$1), AND(EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5)), A1<>F$1)

其中耶稣受难日的日期 (2017 年 4 月 14 日) 被（手动）输入到单元格中 F1。

这个公式可以粗略地分解为

=AND( OR( AND(something1), AND(something2), AND(something3), AND(something4), AND(something5), AND(something6) ), something7 )

在哪里

  • something1 是DAY(A1)=12,A1+1=F$1，测试第一颗子弹（每月 12 日星期四，第二天是耶稣受难日）。
  • something2 是DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1)，用于测试第二颗和第三颗子弹（星期五的第 13 颗或第 14 颗，或者耶稣受难日前一天（星期四））。
  • something3 是DAY(A1)=15,WEEKDAY(A1,2)<=5)，测试第四个项目符号（工作日的第 15 个）。
  • something4 是EOMONTH(A1,0)-A1=3,A1+1=F$1，它测试第五个项目符号（每月的倒数第四天，第二天是耶稣受难日）。
  • something5 是EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1)，测试第 6 和第 7 个项目符号（每月倒数第二天或第三天，即星期五或耶稣受难日的前一天）。
  • something6 是A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5，测试第八个项目符号（月份的最后一天，在工作日）。
  • something7是A1<>F$1，测试今天是否是耶稣受难日并将其排除。如果没有这个测试，我们将突出显示（颜色/阴影）4 月 13 日星期四和4 月 14 日，星期五。

为了测试这一点，您可能需要设置F1为 2017 年的以下每个日期，并验证是否突出显示了正确的日期。

  • 今年真正的耶稣受难日是：4 月 14 日，因此发薪日定在 4 月 13 日星期四。
  • 1 月 13 日，这将迫使发薪日变为 1 月 12 日星期四。
  • 9 月 15 日（或 12 月 15 日），这将迫使发薪日变为 14 日星期四。
  • 4 月 28 日星期五，这意味着发薪日将改为 4 月 27 日星期四。
  • 9 月 29 日，这意味着发薪日将改为 9 月 28 日星期四。
  • 3 月 31 日（或 6 月 30 日），这将强制发薪日为该月的倒数第二天。

注意：如果F1设置为非星期五的日期，则会显示不正确的结果（例如，一个月内超过两次发薪日）。

相关内容