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，存放自定义节假日的一片单元格区域，这部分日期除了周末，也要减掉。

先看实例：

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 开始；

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