用函数计算母亲节的日期(如何使用时间函数计算年龄)

2021年5月9日,是母亲节,是一个感谢母亲的节日!我国的母亲花是萱草花,又叫忘忧草!记得给你们的母亲送份礼物,或者打个电话。

母亲节是每年公历5月的第二个星期日,也就是说,每年母亲节都不是在同一天,那么我们该如何计算某年的母亲节日期呢?


方法一:WEEKDAY函数

1、函数作用

参照某个日期,返回日期在一周中的第几天;

2、函数用法

WEEKDAY(日期,返回值类型)

如何用函数计算母亲节的日期

WEEKDAY函数的返回值类型参数说明

3、解决思路

要计算母亲节日期,首先年份的已知(比如2021年),月份也能确定是5月,剩下的就是确定日期。

①由于母亲节是第二个星期日,我们可以将5月1日~5月30日用函数全部换算成星期,这里需要借助【ROW】、【INDIRECT】、【DATE】函数,公式如下:

=WEEKDAY(ROW(INDIRECT(DATE(2021,5,1)&":"&DATE(2021,5,31))),2)

如何用函数计算母亲节的日期

公式下方黑色区域为公式的结果

②找到第二个7所在的位置,就是母亲节的日期。这里需要借助【SMALL】、【IF】、【ROW】函数组合。公式如下:

=SMALL(IF(WEEKDAY(ROW(INDIRECT(DATE(2021,5,1)&":"&DATE(2021,5,31))),2)=7,ROW(1:31),4^8),2)

如何用函数计算母亲节的日期

用公式获取日期

这里的SMLL+IF函数的经典组合,先用IF判断2021年5月1号~5月31号每天的星期是否等于7。如果等于7,则返回1~31中对应的数;如果不等7,则返回4^8(即65536),然后用SMALL函数提取第二小的数。

③将年份、月份、获取的日期组合起来,就得到了母亲节的日期。公式如下:

=A2&"-5-"&SMALL(IF(WEEKDAY(ROW(INDIRECT(DATE(A2,5,1)&":"&DATE(A2,5,31))),2)=7,ROW(1:31),4^8),2)

如何用函数计算母亲节的日期

完整的计算公式


方法二:穷举

1、解决思路

5月1号,有可能处于星期1到星期日的任意一天,那么我们直接把所有情况列出来,就会得到下面的数据:

如何用函数计算母亲节的日期

母亲节所有可能的日期

2、函数公式

从上表可以看出,母亲节只有可能在5月8号至5月14号这几天中,具体是哪天,就要看5月1号是星期几,在这个思路下,可以得到如下公式:

①公式一:CHOOSE函数

=A2&"-5-"&CHOOSE(WEEKDAY(DATE(A2,5,1),2),14,13,12,11,10,9,8)

如何用函数计算母亲节的日期

②公式二:INDEX函数

=A2&"-5-"&INDEX(15-ROW(1:7),WEEKDAY(DATE(A2,5,1),2))

如何用函数计算母亲节的日期

根据这个思路,还可以写出一些函数公式,这里就不一一列举了,有兴趣的朋友可以继续试下。


方法三:CELLING函数

1、函数作用

将要舍入的数据,沿绝对值增大的方向,向上舍入为指定的基数的倍数,并返回结果。

2、函数用法

CEILING(要舍入的数值,指定的基数)

如何用函数计算母亲节的日期

3、解决思路

因为excel的默认的是是1900年日期系统,在excel的日期系统中,第一天是1900-1-1,为星期日。(1900-1-1实际应为星期一,这里提前了一天,是由于最开始设计日期系统的程序员把1900年当作了闰年,即1900年的2月有29天,为纠正这个错误,就把1900-1-1的星期往前移一天,到1900-3-1时,日期和星期就能全部对上了)

如何用函数计算母亲节的日期

因为日期系统第一天(1900-1-1)是星期日;1900-1-7 是星期六,所以凡是7的倍数的日期序列都是星期六。那么用函数CEILING(date(年,5,1))就可以计算出5月的第一个星期六是几号,在这个日期的基础上加上1天就是第一个星期日,再加上7天就是第二个星期日。最后可以得到下面的公式:

=CEILING(DATE(A2,5,1),7)+1+7

将公式简化,结果如下:

=CEILING(A2&"-5-1",7)+8

4、题外

EXCEL中,还有一个【FLOOR】函数,和【CEILING】函数类似,只是【CEILING】函数向上取舍,【FLOOR】函数向下取舍。根据上面的思路和原理,也可以用【FLOOR】函数来写这个公式结果如下:(有兴趣的朋友可以思考下)

=FLOOR(A2&"-4-30",7)+15

有了以上方法可思路,我们还可以计算父亲节、感恩节的具体日期,不知道大家都学会了吗?

今天的内容就分享到这里,祝我的母亲身体健康,开心快乐。祝福天下母亲:母亲节快乐!

本网页内容旨在传播知识,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:dandanxi6@qq.com

(0)
上一篇 2023-06-05 16:47
下一篇 2023-06-06 11:39

相关推荐