excel - 参考表和比赛日期公众假期

Question

我有一个看起来像这样的表（命名范围PubHols）：

Holiday               2018          2019        2020       2021

New Year's Day      01/01/2018  01/01/2019  01/01/2020  01/01/2021

Australia Day       26/01/2018  28/01/2019  27/01/2020  26/01/2021

Good Friday         30/03/2018  19/04/2019  10/04/2020  02/04/2021

Holy Saturday       31/03/2018  20/04/2019  11/04/2020  03/04/2021

Easter Sunday       01/04/2018  21/04/2019  12/04/2020  04/04/2021

Easter Monday       02/04/2018  22/04/2019  13/04/2020  05/04/2021

ANZAC Day           25/04/2018  25/04/2019  25/04/2020  25/04/2021

Queen's Birthday    11/06/2018  10/06/2019  08/06/2020  14/06/2021

Labour Day          01/10/2018  07/10/2019  05/10/2020  04/10/2021

Christmas Day       25/12/2018  25/12/2019  25/12/2020  25/12/2021

Boxing Day          26/12/2018  26/12/2019  26/12/2020  26/12/2021

试图合并到一个函数中 - 即首先检查日期是否是公共假期，然后运行 ​​ifs。

• 目前的公式是： =IF($A2="","",IFS(WEEKDAY($A2)=1,"Sun",WEEKDAY($A2)=2,"Weekday",WEEKDAY($A2)=3,"Weekday",WEEKDAY($A2)=4,"Weekday",WEEKDAY($A2)=5,"Weekday",WEEKDAY($A2)=6,"Weekday",WEEKDAY($A2)=7,"Saturday"))

标签： excel

Answer 1

您可以将公共假期的测试放入您现有的公式中，如下所示：

=IFS($A$2="","",COUNTIF(PubHols,A2),"Public Holiday",WEEKDAY($A2)=1,"Sun",WEEKDAY($A2)=2,"Weekday",WEEKDAY($A2)=3,"Weekday",WEEKDAY($A2)=4,"Weekday",WEEKDAY($A2)=5,"Weekday",WEEKDAY($A2)=6,"Weekday",WEEKDAY($A2)=7,"Saturday")

但是有更短的测试日期是否是工作日的方法，例如这里描述的

=WORKDAY(A2-1,1,PubHols)=A2

因此，如果您想知道这一天是工作日、公共假期、星期六还是星期日，您可以使用：

=IFS(A2="","",WORKDAY(A2-1,1,PubHols)=A2,"Working Day",WEEKDAY(A2)=1,"Sun",WEEKDAY(A2)=7,"Sat",TRUE,"Public holiday")

有趣的是，这两个公式给出了不同的结果。我认为原因是公共假期不应该在周末，所以今年 12 月 28 日星期一可能是公共假期，而不是 26 日星期六。