行政人事专员必会Excel技能：身份证号出生日期的11种用法和计算

身份证号从左边开始算第7-14位数字为出生日期，7-10四位数字表示年份，11-12两位数字表示月份，13-14两位数字表示日。根据这一特点，在excel中，可以根据身份证号提取出相关出生日期信息或者是根据出生日期进行相关计算或设置出我们所需要的。

一、快速提取出出生日期

1、Ctrl+E智能填充法：首先将第一个出生日期复制出来，然后通过Ctrl+E智能填充可以快速的从身份证号提取出出生日期。

2、mid函数公式提取：用mid提取函数从身份证号的第7位开始提取出8位数字，也就是出生日期。公式=MID(身份证号单元格,7,8)。

二、将出生日期转换为斜杠日期

1、单元格自定义法：按Ctrl+1调出单元格格式对话框，然后选择数字项下的自定义，在右边的类型下输入“00!/00!/00”,最后单击确定即可。

2、分列法：选中身份证一列单元格，单击选择工具栏上的数据，然后选择分列，调出文本分列向导对话框，在步骤1和步骤2中直接单击下一步，在步骤3列数据类型下选择日期YMD，最后单击完成即可。

3、text函数法：用text函数将8位数字的出生日期转为斜杠日期，公式为=TEXT(出生日期单元格,"00!/00!/00")。

三、将出生日期转换为横杠日期

1、单元格自定义法：按Ctrl+1调出单元格格式对话框，然后选择数字项下的自定义，在右边的类型下输入“00!-00!-00”。

2、text函数法：用text函数将8位数字的出生日期转为横杠日期，公式为=TEXT(出生日期单元格,"00!-00!-00")

四、将出生日期转换为文字日期

1、单元格自定义法：按Ctrl+1调出单元格格式对话框，然后选择数字项下的自定义，在右边的类型下输入“00!年00!月00日”,最后单击确定即可。

2、text函数法：用text函数将8位数字的出生日期转为斜杠日期，公式为=TEXT(出生日期单元格,"00!年00!月00日")

五、从身份证号提取出生日期的年份

1、智能提取+&链接法：通过Ctrl+E提取出身份证号中的出生年份，然后再通过链接符号链接文字“年”，公式=年份单元格&"年"。

2、mid函数法+&链接法：通过mid函数从身份证号的第7位开始提取出4位数字，也就是出生年份，然后再用连接符号连接文字年。公式=MID(身份证号单元格,7,4)&"年"。

六、从身份证号提取出出生日期的月份

1、智能提取+CONCAT函数法：通过Ctrl+E提取出身份证号中的出生月份，然后再通过concat链接文字“月”，公式=CONCAT(月份单元格,"月")

2、mid函数法+CONCAT函数法：通过mid函数从身份证号的第11位开始提取出2位数字，也就是出生月份，然后再用concat连接文字年。公式=CONCAT(MID(身份证单元格,11,2),"月")。

七、从身份证号提取出出生日期的日

1、智能提取法：通过Ctrl+E提取出身份证号中的出生日期的日，但前提是身份号中没有其他数据是与出生日相同的，否则会出错。

2、mid函数法：通过mid函数从身份证号的第13位开始提取出2位数字，也就是出生日期的日。

八、隐藏身份证出生日期

1、Ctrl+E智能填充法：先将第一个身份号复制出来，然后手动将出生日期八位数字变为#号，接着再选中一列单元格，即可将所有身份证号的出生日期隐藏起来。

2、mid函数+SUBSTITUTE函数法：先用mid函数从身份证号提取出出生日期，然后再用SUBSTITUTE函数将身份证号中的出生日期替换成8个#。公式=SUBSTITUTE(身份证号单元格,MID(身份证号单元格,7,8),"########")

九、根据身份证号设置生日提醒

1、先用mid用函数从身份证号中提取出生月份和日，公式=MID(身份证号单元格,11,4)。

2、用text函数将提取出的出生月份和日转为斜杠格式，公式=TEXT(MID(身份证号单元格,11,4),"00!/00")

3、用text函数将当天日期中的月份和日提取出来并设置为斜杠格式，公式=TEXT(TODAY(),"m/d")。

4、用身份证号出生月份和日减去现在的月份和日。公式=TEXT(MID(身份证号单元格,11,4),"00!/00")-TEXT(TODAY(),"m/d")

5、最后用text函数将结果显示出来，如果结果为正数表示生日还没到，返回空值，如果结果为负数表示生日已过，同样返回，如果结果为0，则表示当天过生日，则返回“生日快乐”。公式=TEXT(TEXT(MID(身份证号单元格,11,4),"00!/00")-TEXT(TODAY(),"m/d")," ; ;生日快乐")。