每天一点小技能
职场打怪不得怂
编按:对于财务或人事来说,掌握日期函数是工作的必备能力之一。而如何计算某天是当年或当月的第几周,更是日常工作。今天,小E分享给大家的就是用Excel日期函数计算这个问题方法!下面,面对三种不同的实际情况,将有不同的函数和公式方法来处理……
一、计算某一天是一年中第几周(按实际星期计算)
计算某日期在一年中是第几周,大家可以用WEEKNUM函数。
如下图所示,在B2中输入“=WEEKNUM(A2,1)”后,向下复制填充公式,就可以依次计算出每一个日期对应的是一年中的第几周。
但是把日历调出来,对照上图看一下,就会发现有那么一点小 “问题”:
可以看到,2021年1月3日这一日期,用WEEKNUM函数计算出来的结果是“2”,也就是说,函数把这一天计入在了2021年的第2周。这一点,不符合中国人的使用习惯。该怎么解决这个问题呢?
很简单,只需要将WEEKNUM函数的第二参数写成“2”,就可以了。
知识点:WEEKNUM函数的第二参数如果取“1”的话,是把星期日作为一周中的第1天来计算的;WEEKNUM函数的第二参数如果取“2”的话,是把星期一作为一周中的第1天来计算的。
现在,大家再来试一下。在C2中输入“=WEEKNUM(A2,2)”后,向下填充复制公式,得到的结果如下图所示。
这时,可以看到,2021年1月3日这一日期对应的的周次计算结果已经是1了,也就是说,这一天被计入在了2021年的第1周。
二、计算某一天是一年中第几周(按天数计算)
上面讲了用WEEKNUM函数计算周数的方法,下面的方法则是根据实际的星期来作为判断的。
例如上图中,计算结果为“1”的天数只有2021年1月1日至2021年1月3日这三个日期。这是因为自2021年1月4日开始,变成新的一周了,所以重新开始计算周数。
可是,有的公司实际上是以7天为一个周期来统计的,也就是说,2021年1月1日至2021年1月7日,当作第1周,2021年1月8日至2021年1月14日,当作第2周。
这时所说的“周”,其实把它理解为“7天”更适合(而非自然周)。第1周就是第一年中的第1组7天,第二周就是一年中的第2组7天,其余以此类推。在这种情况下,该如何计算第几周呢?
大家在B2中输入“=ROUNDUP((A2-DATE(YEAR(A2),1,1)+1)/7,0)”后,向下复制填充公式,得到的结果如下图。
这个函数的逻辑是什么呢?一起来分步研究一下。
1.在C2中输入“=YEAR(A2)”。可以看到,它的结果是2021,这是因为YEAR函数的功能就是提取日期中的年份,在本例中即为2021。
2. 将C2中的公式改为“=DATE(YEAR(A2),1,1)”。
DATE函数的作用,就是构造一个日期,它的三个参数分别为年、月、日。
因为需要构造2021年的第一天来参与运算,所以将上面写好的YEAR函数嵌套进DATE函数来作为年的值,再将月、日的值都设定为1,,就可以生成了2021年1月1日这个日期了。
此时,结果显示的是“44197”,它是2021/1/1这个日期对应的数值。(注:如果想显示为日期格式,则可以通过设置单元格格式,将数据设置为日期格式。)
3. 将C2中的公式改为“=A2-DATE(YEAR(A2),1,1)”,计算某一天与这年第一天的天数差值。
效果如下,天数差值已经转换为0、1、2、3、……这样的整数序列。
4. 将C2中的公式改为“=A2-DATE(YEAR(A2),1,1)+1”,就可以得到如下图所示的整数序列。
接下来,要将这列整数序列转换为7个1、7个2、7个3、……这样的周数序号。
5. 将C2中公式改为“=(A2-DATE(YEAR(A2),1,1)+1)/7”。
因为一周的天数是7天,所以要将天数差值+1(即为天数数字)的结果除以7,于是可以得到0.1、0.3、0.4、0.6、……这样的小数序列。(注意:要想得到小数效果,需将小数设置成至少1位小数,否则显示的是整数。)
6. 将C2中的公式改为“=ROUNDUP((A2-DATE(YEAR(A2),1,1)+1)/7,0)”。
它将数据向上取整并保留0位小数(即只保留整数),得出的结果就依次为7个1.0、7个2.0、7个3.0这样的周数序号。(注:如果想显示为整数,只需要将小数位数设置为0即可。)
三、计算某一天是当月第几周(按天数计算)
小伙伴可能还会遇到按照每个月中第几周来进行统计的情况,这就要在每一个月内计算周数序号。
这个计算方式的公式相对简单一点,大家可以在B2中输入“=ROUNDUP(DAY(A2)/7,0)”,然后向下复制填充公式,即可得到预期的结果。
DAY函数,它是提取该日期在当月的天数。如2021年1月1日至2021年1月31日的天数数字,依次为1、2、3、……、31;2021年2月1日至2021年2月28日的天数数字,依次为1、2、3、……、28。这时我们可以看到,通过DAY函数,就已经得到了1、2、3、……这样的整数序列,将这样的整数序列除以7,再通过ROUNDUP函数进行取整并保留0位小数,就可以得到具体的周数序号了。小伙伴们可以自行动手算一下哦!
好了。三种常用的计算第几周的方式,已经讲完了,你学会了吗?
联系客服