哈喽,大家好!
日历随处可见,但你有没有想过怎么用函数来制作呢 。
PS:本次所需技能如下:
1、条件格式设置。
2、隐藏函数DateDif。
3、返回一周中的第几天函数Weekday。
步骤分解:
【1】DATEDIF(1,$B$1&'-'&$D$1,'d')
该步骤主要用好了DateDif函数,主要返回2个日期间的相隔天数,该函数有3参数,DateDif(开始日期,结束日期,要的结果表示),本例中的开始日期是1900-1-1,结束日期是2018年10月1日,结果返回相差的天数'd')
因1900-1-1的序列数为1,故,用1表示;
因2018-8会被Excel识别为2018-8-1,故,只连接了年和月。
此公式得出的结果是43312的序列数,而该序列数对应的日期值是2018-7-31。
各位,重点来了,
2018-7-31在日历中正好是上个月的最后一天。
【2】WEEKDAY(DATEDIF(1,$B$1&'-'&$D$1,'d'),2)
用Weekday函数得出【1】的日期2018-7-31是周几,图中很明显是周二。
【3】IF(WEEKDAY(DATEDIF(1,$B$1&'-'&$D$1,'d'),2)<>
该步骤判断【2】中得出的是周几,即:如果【2】得出日期的星期<>
【4】=IF(WEEKDAY(DATEDIF(1,$B$1&'-'&$D$1,'d'),2)<>
此公式蓝色部分是【1】至【3】得出日历左上角日期的公式,红色部分则是第4个步骤。用日历左上角日期+相应的行数和列数,使其动态的自加减。
Row()*7 当前单元格行号*7,*7是因为左侧相邻日期间隔是7天。
Row()*7-21,-21是因为是从第3行开始的,所以第三行不需要加,所以-21。
如果是从第4行开始的,则Row()*7-28;
如果是从第5行开始的,则Row()*7-35;
如果是从第6行开始的,则Row()*7-42;
以此类推…………
COLUMN()-1,是从第1列开始的,所以-1,
如果是从第2列开始的,则COLUMN()-2;
如果是从第3列开始的,则COLUMN()-3;
以此类推…………
需要注意的地方:
1、对年和月单元格的引用需要“绝对引用”;
本文的核心:
=DATEDIF(1,$B$1&'-'&$D$1,'d')
=DATEDIF(1,'2018-8-1','d') 返回2018-7-31,即上个月的最后一天。
=DATEDIF(1,'2018-11-1','d') 返回2018-10-31,即上个月的最后一天。
另,=TODAY() 返回今天的日期
=DATEDIF(1,TODAY(),'d') 返回昨天的日期。
本文的函数思路:
(1)先用DATEDIF函数得出上月最后一天的日期。
(2)根据上月最后一天,用Weekday函数得出是周几,这样就得出了该日期在日历中显示的“大概”位置。
(3)根据一周不过7天规则,如果上月最后一天是周日(7),则正好是左上角的日期;如果最后一天不是周日(7),则用7减去该日(1到6),则得出左上角日期。
(4)得出左上角日期后,就可以自相加了,函数:ROW()*7-21+COLUMN()-1。
好了,今天的分享到此结束,感谢小伙们们围观捧场。
图文作者:赵中山
联系客服