公民身份号码是特征组合码，由17位数字本体码和1位校验码组成。排列顺序从左至右依次为：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。

地址码（从左至右的第1-6位数字）

表示编码对象初始户口所在县（市、旗、区）的行政区划代码。

出生日期码（从左至右的第7-14位数字）

表示编码对象出生的年、月、日，代码之间不使用分隔符。

顺序码（从左至右的第15-17位数字）

表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

并且由于顺序码是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第2、第3、第4、第5个分配码。

举个例子

005的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001*和003*。

分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。

校验码（最后1位数字）

根据前面17位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。由于篇幅的关系，如果想要知道校验码如何计算的亲可以自行寻找度娘看看唷。

一、根据身份证号码判断出生日期、性别、年龄

（1）出生日期：

假定E2单元格为18位身份证号码所在的单元格，复制以下公式：

“=CONCATENATE(MID(E2,7,4),"-",MID(E2,11,2),"-",MID(E2,13,2))”

以上公式的原理在于CONCATENATE（字符串连接或合并）函数和MID（从一个文本字符串的指定位置开始，截取指定数目的字符）函数的运用。

具体语法

CONCATENATE(Text1,"-",Text2,"-", Text3)

MID(Text,Start_num,Num_chars)

①MID(E2,7,4)意在身份证号码中获取表示年份的数字的字符串，即左起第7位开始，提取4个字符串；

②MID(E2,11,2)意在身份证号码中获取表示月份的数字的字符串，即左起第11位开始，提取2个字符串；

③MID(E2,13,2)意在身份证号码中获取表示日期的数字的字符串，即左起第13位开始，提取2个字符串；

④=CONCATENATE(MID(E2,7,4),"-",MID(E2,11,2),"-",MID(E2,13,2))

目的就是将①、②、③中提取的字符串合并在同一个单元格内显示。

（2）性别：

假定E2单元格为身份证号码所在的单元格，复制以下公式至单元格

“=IF(MOD(MID(E2,17,1),2),"男","女")”

以上公式的原理在于IF（判断真假，本段中可理解为判断男女）函数和MOD（求余）函数、MID函数的嵌套运用。

具体语法

IF(Logical_test,Value_if_true,Value_if_false)

MOD(Number,Divisor)

MID(Text,Start_num,Num_chars)

①MID(A1,17,1)意在身份证号码中获取表示性别的字符串，即左起第17位开始，提取1个字符串。

②根据身份证号码的编号规则，我们知道，奇数为男性，偶数为女性。为了方便在公式中体现，我们可将奇数（即男性）视为“Value_if_true”，将偶数（即女性）视为“Value_if_false”。

利用MOD函数，取①除以2的余数，公式可写成MOD(MID(A1,17,1),2)。

③由上可得，若余数=1，第17位就是奇数（即男性）；若余数=0，第17位就是偶数（即女性）。于是总体公式就是

=IF(MOD(MID(A1,17,1),2),"男","女")。

（3）年龄

假定E2单元格为身份证号码所在的单元格，复制以下公式至单元格“=YEAR(NOW())-MID(E2,7,4)”

PS：此公式不另设单元格格式，且不对单元格进行四舍五入。小仙女可放心使用。

以上公式的原理主要运用到YEAR函数（将系列数转换为年）和MID函数的运用。

具体语法

YEAR(Serial_number)

MID(Text,Start_num,Num_chars)

①使用YEAR函数计算年龄，第一步首先要知道当前的年份。

在所求单元格内输入=YEAR(NOW())，就可以直接求出今年的年份为2017年。

②在得出当前年份后，我们还需要在身份证号码中提取出的代表年份的字符串，于是可照搬公式MID(E2,7,4)。

③合并上述两个公式，则得出计算年龄总公式=YEAR(NOW())-MID(E2,7,4)。

由于此公式是直接计算年份，好处是不需要另外去设置单元格格式，并且不会产生后续的小数点，坏处就是基本上都会算少了一岁。如果对此表示不满意的小仙女和小鲜肉们，可直接在公式后手动+1即可。

二、根据身份证号码判断初始户籍所在地

由于身份证号码的前6位代表了初始户口所在县（市、旗、区）的行政区域代码，所以利用Vlookup（纵向查找）函数公式和辅助工作表即可完成此项。

假定E2单元格为身份证号码所在的单元格，Sheet2工作表为辅助工作表，Sheet2中A列为行政区域代码，B列为对应行政区域名称，

复制以下公式至单元格

=VLOOKUP(MID(E2,1,6)*1,Sheet2!$A$1:$B$9999,2,0)

以上公式的原理主要是利用Vlookup函数和MID函数的嵌套运用。

具体语法

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)

MID(Text,Start_num,Num_chars)

①首先在同一个Excel工作簿内建立含有行政区域代码和名称的辅助工作表，根据上面的公式，辅助工作表的名称为Sheet2，辅助表中A列对应为行政区域代码，B列对应为对应行政区域名称。

②MID(E2,1,6)意在身份证号码中获取表示地址码的字符串，即左起第1位开始，提取6个字符串；

③使用Vlookup函数，在提取字符串后，使用字符串与辅助表Sheet2的A、B列数据进行引用，

从而得出总公式

=VLOOKUP(MID(E2,1,6)*1,Sheet2!$A$1:$B$9999,2,0)。

根据入职日期计算转正日期

在我们计算转正日期之前，首先要做的一件事，就是要清楚公司目前规定的转正时间。

以下分别给大家列举两种情况的Excel公式：

假定新员工2017年7月27日入职，D2单元格的内容为2017-07-27。

（1）入职满3月后转正，可直接使用DATE公式进行加减。

=DATE(YEAR(D2),MONTH(D2)+3,DAY(D2))

若是入职满1月、2月转正的，可直接将+3修改为+1、+2，以此类推。

同理可得，若是按天数去计算转正的，可以将+号挪移至DAY(D2)后，填写上转正的天数即可。

（2）当月10日前入职，试用期2个自然月；当月10日之后入职，首月不计算入试用期，试用期3个自然月。以每月的1日作为统一的转正日期。

假定D2单元格为入职日期所在的单元格，复制以下公式至单元格

=IF(DAY(D2)>10,DATE(YEAR(D2),MONTH(D2)+3,1),DATE(YEAR(D2),MONTH(D2)+2,1))

以上公式原理最关键的点，就是使用了IF函数的判断原理。

具体语法

IF(Logical_test,Value_if_true,Value_if_false)

DATE(YEAR(Text),MONTH(Text),DAY(Text))

若日期DAY大于10，则返回DATE(YEAR(D2),MONTH(D2)+3,1)的计算值；

若日期DAY不大于10，则返回DATE(YEAR(D2),MONTH(D2)+2,1)的计算值。

此公式的操作可根据实际情况进行变更，只需要修改、变动“>”号、“+”号的位置和具体值即可。

根据入职日期计算劳动合同签订、到期时间

作为每N年1签的劳动合同，我们也是可以利用Excel的DATE函数去计算签订日期、到期日期，甚至可以根据续签的年份，来变动相对应的日期。

那么这里的公式又是怎么样的呢？

假定新员工2017年7月27日入职，D2单元格的内容为2017-07-27。

签订3年的劳动合同，O2单元格为签订时间，P2单元格为到期时间，Q2单元格为登记续签年份。

签订时间公式：

=DATE(YEAR(D2)+ Q2,MONTH(D2),DAY(D2))

到期时间公式：

=DATE(YEAR(O2)+3,MONTH(O2),DAY(O2)-1)

这两个公式的运用的原理如同本文第三项。到期时间公式的颜色“+”号、“-”号同样可以变更为具体所需的数据。

其中，Q2单元格作为登记续签年份的存在，是需要手动的进行更新。其余的签订时间、到期时间单元格均可根据Q2的变动而变动。

有些小仙女可能会问，为什么需要在日期函数DAY后-1呢？

这出于计算一整个年度的考虑，往往劳动合同到期日均比签订日提前一天。在实际操作中视为同一日的，也可直接根据实际对公式进行更改。

文末黑科技

在HR的实际操作过程中，我们往往会遇到各种各样需要嵌套的Excel公式，而这些公式如果要一个一个解释起来，除了篇幅很长之外就不具有太大的意义了。毕竟，我们只要复制粘贴就好了。

所以，我特地将两个很繁琐的公式放在文末，送给大家（才不是懒得写解析呢）。

黑科技之一

利用身份证号判断退休年龄

假定E2单元格为18位身份证号码所在的单元格，复制以下公式至单元格

=DATE(MID(E2,7,4)+IF(MOD(MID(E2,17,1),2)=0,55,60),MID(E2,11,2),MID(A1,13,2))

以上就是DATE函数、MID函数、IF函数、MOD函数嵌套运用。

不算长吧？嘿嘿~

黑科技之二

判断所输入身份证号码是否正确

假定E2单元格为18位身份证号码所在的单元格，复制以下公式至单元格

=IF(MID("10X98765432",(MOD(SUM(MID(E2,1,1)*7,MID(E2,2,1)*9,MID(E2,3,1)*10,MID(E2,4,1)*5,MID(E2,5,1)*8,MID(E2,6,1)*4,MID(E2,7,1)*2,MID(E2,8,1),MID(E2,9,1)*6,MID(E2,10,1)*3,MID(E2,11,1)*7,MID(E2,12,1)*9,MID(E2,13,1)*10,MID(E2,14,1)*5,MID(E2,15,1)*8,MID(E2,16,1)*4,MID(E2,17,1)*2),11)+1),1)=(MID(E2,18,1)),"正确","错误")

呃…这里面可见的有IF/MID/MOD/SUM函数，原理？不重要。能用就好。

-END-

感谢本文作者

同薪粉--凝凝

其他宝宝如果也想分享自己的小技巧

欢迎来找小薪哦

��返回搜狐，查看更多