小瞧日期函数的都被打脸了！同事用这个函数做Excel时间表，效率提高35%！

★

编按

★

大家好呀，这里是小E！今天小E要跟大家吹爆这个看起来复杂实际上好用到飞起的计算周次的WEEKNUM函数。WEEKNUM函数属于日期函数中较为复杂的函数，在计算某个日期是第几周时尤其好用，计算成百上千行数据的日期，别人要用半天甚至一天，而你用这个函数只需要几分钟就搞定了！接下来我们就去学习一下这个函数的用法吧，还有老师为你层层拆解公式逻辑哦~

小马是某连锁企业的活动策划专员，7月份针对公司六个片区的近300+店面设置了活动计划。活动计划表如图所示，有活动起止日期、区域以及门店代码。其他信息与我们今天要讨论的问题基本无关，就不列出来了。

扫码入群，下载Excel练习文件

活动时间从7月2日到8月1日，每期活动基本在一周之内，具体的活动周期如图所示。

当小马把完成的活动计划表发给经理以后，经理提出两个优化建议：

第一，增加周次和周内排期两列，效果如下图所示；

第二，筛选一个区域的最终结果，效果如下图所示。

小马听明白经理的要求以后，马上开始行动，但是才弄了几十行就头大了。全部弄完要将近两千行数据，这得一上午吧……

实际上解决这个问题有两条路，公式法和操作法。

从使用角度来说，操作法效率其实更高，但是也不能浪费这样一个学习公式函数的好机会，所以咱们先来聊聊公式法。

计算周的公式：

=TEXT(WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)-26,"第[DBNum1]0周")

周内排期的公式：

=TEXT(--SUBSTITUTE(LEFT(D2,FIND("-",D2)-1),".","-"),"周aaa")&"--"&TEXT(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),"周aaa")

公式看起来有点长，但是逻辑不复杂。先来看第一个公式，第一个需要解决的问题是：根据活动时间段标出对应的周次。

1

提取结束日期

说起周次，熟悉日期函数的同学应该会想到WEEKNUM函数，这个函数的功能是：计算某个日期是在一年中的第几周。但本例中计算的周次并不是一个具体日期，而是一个日期区间7.2-7.4。

【第一步】要从这个日期区间先把日期提取出来，咱们就取结束日期；

从数据中可以发现结束日期都是“-”这个符号之后的内容。因此就能用MID和FIND这个组合套路，关于这个组合的用法，可以参考往期教程：

Excel教程：FIND+MID函数，乱序产品规格中提取型号

提取结束日期的公式为：=MID(D2,FIND("-",D2)+1,9)

【第二步】上图日期的写法是用小数点分隔月和日。在Excel中，只有用“-”和“/”分隔的才是标准日期。因此还需要对提取出来的日期做个处理，把小数点换成“-”，这就得用SUBSTITUTE函数实现。

关于这个教程的用法参考往期教程：

Excel教程：SUBSTITUTE函数解决文本单元格的判断和计算，你会吗？

这部分的公式是：=SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")

【第三步】现在看上去像是日期的标准写法了，但实际还是文本格式，需要继续转化。高手常用的方法是在公式最前面加两个减号，也就是=--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-")

这样G2单元格里的数据就变成一个数字了，我们只需要把单元格格式改成日期格式就OK。至此就完成了结束日期的提取。顺带一提，其实公式是不受格式影响的，在公式中不管是按数字显示还是按日期显示，最终的计算结果都一样的。

2

计算周次

接下来解释周次的问题，周次是这项工作中最让人头大的一个问题。

【第一步】为了便于大家理解，需要把整个活动中涉及到的时间区间先列举出来，再结合一个7月份的日历，来发现其中的规律。

可以看出，共有7个活动时间段，分别分布在五周。从我们前面提取出的每期活动的结束日期来看，有周一、周四、周日结束三种情况，每期活动基本都是3-4天。

要将同一档活动归结在某一周的话，这里的周就不能是周一到周日这种习惯上的划分标准了。比较符合要求的是周二开始到周一结束这样的划分方式。

【第二步】恰好在WEEKNUM函数中，第二参数就可以灵活设置每周的起始点。如果要用周二作为一周的起点，第二参数使用12即可。

WEEKNUM函数的规则为：WEEKNUM(日期,返回类型)，其中返回类型就是选择周几作为一周的开始，共有以下选项：

公式为=WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)

【第三步】上面的步骤得到的是当年的第几周，而不是活动中的第几周。第一周显示的是27，需要在结果后面-26作为调整。

所以最终计算周次的公式为：=WEEKNUM(--SUBSTITUTE(MID(D2,FIND("-",D2)+1,9),".","-"),12)-26

重要说明：在计算周次的问题中，往往需要根据具体情况和实际需求去调整参数值，并且需要做加减量使得结果与实际相符，本例中的“12”和“-26”就是基于实际需要才得以确定的。

3

调整显示格式

折腾了半天只是得到一个数字，而且还不是第几周的这种格式，需要调整。这就是TEXT函数的拿手菜了。

在单元格中输入函数：=TEXT(H2,"第[DBNum1]0周")就可以实现格式转化。

需要重点说明的是第二参数格式代码的含义。

在"第[DBNum1]0周"中，0是数字占位符，必须要的。[DBNum1]是指定数字的大写格式，可以试试将里面的1改成2、3、4能得到什么结果。“第”和“周”是数字前后的固定文字，没有特殊含义。最终就是第几周这样的格式了。

再来回顾一下这个公式，其实就三步：提取结束日期、计算周次、调整显示格式。但是其中所包含的信息量却是非常大的，值得每一位想学函数的同学去仔细研究。

关于第二个公式的讲解和针对本问题的操作法，咱们下次接着聊。