夜雨聆风学习资料网

ARTICLE · 1061128

Excel中计算年龄、出生日期、工作日天数和合同到期提醒等

Excel中计算年龄、出生日期、工作日天数和合同到期提醒等

目录

一、根据出生日期计算年龄

二、根据身份证取出生日期

三、计算父亲节日期

四、计算工作日天数

五、30天内到期合同

一、根据出生日期计算年龄

先看实例:

1、两个主函数

① DATEDIF(核心函数:算两个日期之间差多少)

语法:DATEDIF(开始日期,结束日期,计算单位)

·参数 1:开始日期 → A3,就是出生日期

·参数2:结束日期→ 用TODAY() 获取今天的系统日期

·参数 3:"y" 代表整年(y=year 年;m=月;d=日)

② TODAY() 函数

语法:TODAY(),括号里面不用写任何东西

自动抓取电脑系统的今天日期,每天打开表格,这个日期会自动更新。

二、根据身份证取出生日期

先看实例:

涉及两个函数

① MID 函数(截取字符串)

语法:MID(要截取的单元格,从第几位开始,截取几个字符)

·参数1:A11,存放 18 位身份证号码

·参数2:7,从第 7 个字符开始(18 位身份证:前6 位是地区,第7 位开始才是出生年月日)

·参数3:8,一共截取 8 个数字

② TEXT 函数(改数字显示格式)

语法:TEXT(要改格式的内容,"格式模板")

·参数1:MID截取出来的8 位数字19890625

·参数2:"0-00-00"格式模板,把8 位数字变成年-月-日样子

三、计算父亲节日期

先看实例:

背景:每年 6 月的第三个星期日是父亲节

完整公式:=A19+7-WEEKDAY(A19,2)+7*2

A19 是当年的 6 月 1 日

核心函数 WEEKDAY

语法:WEEKDAY(日期,第二参数)

·参数1:A19,要判断星期的日期(6 月 1 日)

·参数2:2模式规则:周一= 1,周二= 2 …… 周日= 7(国内最常用模式)

第①部分:A19+7-WEEKDAY(A19,2)

目标:算出 6 月第一个星期日。拿例子2022-6-1(星期三,WEEKDAY结果= 3):2022-6-1 + 7 − 3 = 2022-6-5,正好就是6 月第一个周日。

第②部分:+7*2

7*2 等于 14 天,两周。第一个星期日,再加 14 天,直接跳到第三个星期日,就是父亲节。

四、计算工作日天数

先看实例:

公式解读:=NETWORKDAYS(A39,B39,A43:D44)

用途:计算两个日期之间的工作日,自动刨除周六、周日,还可以再额外扣除法定节假日、公司自定义假期。

主函数NETWORKDAYS

语法:NETWORKDAYS(开始日期,结束日期,[要额外扣除的节假日区域])

·参数1:A39 → 开始日期,例子是2020/7/1

·参数2:B39 → 结束日期,例子是2020/10/31

·参数3(可选):A43:D44,存放自定义节假日的一片单元格区域,这部分日期除了周末,也要减掉。

五、30天内到期合同

先看实例:

1. FILTER 函数

语法:FILTER(数据区域,筛选条件,[无匹配结果时显示文本])

·第1 参数A52:D55:要输出的完整数据区域(4列全部信息)

·第2 参数:数组条件,多条件用* 相乘,等价AND,全部条件同时满足才成立

·第3 参数"无到期合同":可选容错;一条都没筛选出来,不会报错,直接显示这句话。

2. TODAY()

无参数,获取系统当前日期,表格打开自动更新。

分步拆解整公式

·C52:C55-TODAY():逐行计算【合同到期日-今日】,算出剩余天数

·A002:28;A004:279;A005:23;A006:-4

·条件1:>=0,筛掉负数(过期合同 A006 被排除)

·条件2:<30,筛掉大于等于 30 天的(A004:279被排除)

·* 同时满足,保留 A002(28)、A005(23)两行

·FILTER 提取这两行整表数据,溢出输出到 A58 开始;

·如果没有符合条件行,单元格输出文字:无到期合同。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料