手把手教你，学会星期相关函数

在Excel 2016中，用于处理星期的函数主要包括WEEKDAY、WEEKNUM及ISOWEEKNUM函数。除此之外，也经常用MOD函数和TEXT函数完成星期值的处理。

用W EEKDAY函数返回指定日期的星期值

WEEKDAY函数返回对应于某个日期的一周中的第几天。默认情况下，天数是 1（星期日） ~7（星期六）的整数，该函数的基本语法如下。

return_type参数用于确定返回值的类型，不同的参数对应返回值的类型如表13-6所示。

WEEKDAY函数的第二参数使用2时，返回数字1~7分别表示星期一至星期日。以下公式可以返回系统当前年份的1月1日是星期几。

如果系统当前年份为2018年，公式结果将返回1，即星期一。

示例13-35   计算指定日期是星期几

如图13-59所示，分别使用不同函数判断B1:H1单元格中的日期是星期几。

B2单元格公式为：

=WEEKDAY(B1,2)

WEEKDAY函数的第二参数为2，返回1~7的数字，表示从星期一到星期日为一周。

B3单元格公式为：

=MOD(B1-2,7)+1

MOD函数根据每周均由星期一到星期日7天循环的原理，计算日期与7相除的余数。MOD函数中被除数减2结果+1，返回结果与WEEKDAY函数相同的数值。

B4:B7单元格公式分别如下。

=TEXT(B1,'aaaa')

=TEXT(B1,'aaa')

=TEXT(B1,'dddd')

=TEXT(B1,'ddd')

TEXT函数的第二参数利用了Excel的内置数字格式代码。

第二参数使用'aaaa'时，返回中文“星期一”。

第二参数使用'aaa'时，返回中文星期简写“一”。

第二参数使用'dddd'时，返回英文“Monday”。

第二参数使用'ddd'时，返回英文星期简写“Mon”。

星期有关的计算

示例13-36    计算员工每月考评日期

某公司规定，每月20日为员工固定考评日，如果恰逢20日是周六或周日，则提前至周五考评。如图13-60所示，需要根据A列中的月份，计算出每月考评的日期。

在B3单元格中输入以下公式，并向下复制到B14单元格。

=DATE(2018,A3,20)-TEXT(WEEKDAY(DATE(2018,A3,20),2)-5,'0;!0;!0')

首先用“DATE(2018,A3,20)”组成一个日期，该日期年份为2018，月份由A3单元格指定，一月中的天数为20。再用WEEKDAY函数计算出该日期是星期几。

用WEEKDAY的计算结果减去5之后，如果日期是星期六，则结果为1。如果日期是星期日，则结果为2，如果日期是星期一到星期五，则显示为负数或零。

TEXT函数使用格式代码“'0;!0;!0'”，将正数部分显示为原有的值，将负数和零强制显示为0。

最后用“DATE(2018,A3,20)”减去TEXT函数的计算结果，如果日期是星期一到星期五，则减去0；如果日期是星期六，则减去1；如果日期是星期日，则减去2，最终得到实际考评日期。

示例13-37    计算指定日期所在月份有几个星期日

如图13-61所示，需要计算A列日期所在月份有几个星期日。

在B2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键，向下复制到B7单元格。

{=COUNT(0/(WEEKDAY(TEXT(A2,'e-m')&-ROW

($1:$31),2)=7))}

首先，用TEXT函数返回A2单元格的日期“年-月”，再用文本连接符与“ROW($1:$31)”连接，得到一组日期样式的字符串。

{'2016-6-1';'2016-6-2';'2016-6-3';……;'2016-6-30';'2016-6-31'}

然后，用WEEKDAY函数依次判断这些字符串是星期几，对于实际不存在的日期，如2016-6-31，将返回错误值#VALUE!，得到内存数组结果为：

{3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;#VALUE!}

再用等式判断以上结果是否等于7，得到由逻辑值TRUE、FALSE及错误值构成的新内存数组。

{FALSE;FALSE;FALSE;FALSE;TRUE;……;#VALUE!}

接下来用0除以以上内存数组，0除以TRUE结果为0，0除以FALSE和错误值，结果为错误值#DIV/0!和#VALUE!。

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;……;#VALUE!}

最后，用COUNT函数统计其中的数值个数，得到的结果就是每个月的星期日天数。

也可以使用以下数组公式完成同样的计算。

{=COUNT(0/(MOD(TEXT(A2,'e-m')&-ROW($1:$31),7)=1))}

先用TEXT函数构成1~31日的日期样式的字符串。

再用MOD函数计算日期字符串与7相除的余数，如果日期为星期日，MOD函数结果为1。对于不存在的日期返回错误值#VALUE!。

接下来用等式判断MOD函数的结果是否等于1，返回由逻辑值和错误值#VALUE!构成的内存数组。

0除以内存数组结果，最后使用COUNT函数计算相除后的数值个数。

使用WEEKNUM函数判断周数

WEEKNUM函数返回指定日期属于全年的第几周，该函数的语法结构与WEEKDAY函数的语法结构完全相同。因为习惯上把星期一到星期日算作一周，所以通常将WEEKNUM函数的return_type参数设置为2。

ISOWEEKNUM函数用于返回给定日期在全年中的ISO周数。

ISO 8601是国际标准化组织的国际标准日期和时间表示方法，主要在欧洲流行。根据ISO 8601的规则，每年有52周或53周，每周的周一是该周的第1天。每年的第一周为该年度的第一个周四所在的周。例如，2017年1月5日为当年的第一个周四，那么2017年1月2日至2017年1月8日为2017年的第一周。

示例13-38    判断指定日期是当年的第几周

如图13-62所示，分别使用WEEKNUM函数和ISOWEEKNUM函数，判断A列日期是该年的第几周。

在B2单元格中输入以下公式，并向下复制到B7单元格。

=WEEKNUM(A2,2)

WEEKNUM函数将包含1月1日的周识别为该年的第1周，A5单元格中的2017年1月1日被判断为该年度的第1周。

在C2单元格中输入以下公式，并向下复制到C8单元格。

=ISOWEEKNUM(A2)

ISOWEEKNUM函数将包含该年第一个星期四的周识别为第1周。2017年1月1日为星期日，因此判断为上年度的最后一周。

使用WEEKDAY和TODAY函数返回过去最近的星期日

示例13-39    返回过去最近星期日的日期

以下公式将返回当前日期上一个星期日的日期，如果当前日期是星期日，则返回前一个星期日的日期。如果系统日期为2018年1月1日，公式结果如图13-63所示。

=TODAY()-WEEKDAY(TODAY(),2)

“WEEKDAY(TODAY( ),2)”部分返回系统当前日期的星期值，用当前日期减去当前日期的星期值，得到上一个星期日的日期。

同理，以下公式将返回当前日期下一个星期日的日期，如果当前日期是星期日，则返回当前的日期。

=TODAY()-WEEKDAY(TODAY(),2)+7

计算指定年份母亲节的日期

每年5月份的第二个星期日是母亲节，利用星期类函数可以计算出指定年份母亲节的日期。

示例13-40    计算母亲节的日期

如图13-64所示，需要根据A列的年份计算出该年母亲节的日期。

在B2单元格中使用以下公式。

=(A2&'-5-1')-WEEKDAY(A2&'-5-1',2)+14

首先将A2与字符串“-5-1”连接，得到能够被Excel识别为日期的新字符串“2015-5-1”。使用WEEKDAY函数返回表示2015年5月1日星期的数值5，再用“2015-5-1”减去当天的星期值，得到上一个星期日的日期。最后加上14天，计算出该年5月份的第二个星期日，即母亲节的日期。