身份证是居民的身份象征，在各个领域都要用到身份证。身份证中可以提取许多的相关信息，然而大多数人都不知道如何应用身份证号来提取有用信息。下面，小编教大家用Excel提取出生日期、性别、年龄及户口所在地等信息。

判断身份证号码是否是18位

输入身份证号码时需要将单元格格式设置为文本。工作时有可能会将身份证号码输错，多输入或者少输入。所以，首先要判断身份证号码是否是18位，再提取身份证信息。也可以在计算公式中判断，但输入公式太多，容易出错。

公式“=IF(LEN(A3)=18,"是","否")”返回“是”即身份证号码为18位，反之则不是18位，输入错误。

LEN函数：得到字符串的长度。=LEN(A3)返回18表示A3单元格字符串长度是18。

出生日期

身份证号码是：622924199605211012第7位开始往后的4位是出生的年，第11位往后两位是出生的月份，第13位往后两位是出生日期。

公式“=1*MID(A3,7,4)”提取年份，从A3单元格第7个数字开始截取4个数字。

MID函数：一个字符串中截取出指定数量的字符，MID(A3,7,4)返回1996表示从A3单元格第7个数字开始截取4个数字。

=1*MID(A3,7,4)表示将MID(A3,7,4)提取的文本数据转为数值数据。

公式“=1*MID(A3,11,2)”提取月份，从A3单元格第11个数字开始截取2个数字

公式“=1*MID(A3,13,2)”提取日期，从A3单元格第13个数字开始截取2个数字

出生日期“=DATE(C3,D3,E3)”

或者直接用公式

“=DATE(1*MID(A3,7,4),1*MID(A3,11,2),1*MID(A3,13,2))”提取出生日期

年龄

实岁计算

=TRUNC((TODAY()-E3)/365)，当前日期减出生日期再除以365，然后用TRUNC函数去掉结果的小数部分

TRUNC函数：作用是去掉结果的小数部分。

TODAY函数：取得当前系统日期，不需要参数。

虚岁计算

=YEAR(TODAY())+1-YEAR(F3)

YEAR函数：返回某个日期的年份，YEAR(F3)返回1996表示出生年份是1996年。

实岁换算成虚岁：在本人生日到来之前，虚岁=实岁+2；在本人生日到来及以后，虚岁=实岁+1。

性别

方法一

身份证号码第17位是单数代表男性，双数代表女性。

=IF(ISEVEN(MID(A3,17,1))=TRUE,"女","男")

ISEVEN函数：ISEVEN(MID(A3,17,1))=TRUE表示身份证号码第17位是双数，为女性；ISEVEN(MID(A3,17,1))=FALSE表示身份证号码第17位是单数，为男性。

方法二

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

MOD函数：两个数值表达式作除法运算后的余数，MOD(MID(A3,17,1),2)表示第17位数除以2，返回1则为男性，反之则为女性。

户口所在地

地区编码：身份证地区编码信息可直接在网上查找

城市

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)

LEFT函数：一个字符串的左边截取出指定数量的字符，LEFT(A2,4)表示从A2单元格最左边开始，截取4个字符

VLOOKUP函数：一个查找函数

VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)表示在地区编码!A2:B3559区域内匹配身份证前4位，采用模糊查找返回“地区编码!A2:B3559”的第二列。

注意：此处必须用1*LEFT(A2,4)将LEFT(A2,4)提取的文本数据转化为数值数据才能用VLOOKUP函数查找

区/县

=VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

=B2&C2

或者直接提取户籍信息：

=VLOOKUP(1*LEFT(A2,4),地区编码!A2:B3559,2,0)&VLOOKUP(1*LEFT(A2,6),地区编码!A2:B3559,2,0)

以上就是用Excel提取出生日期、性别、年龄、及户口所在地等信息的具体方法，视频操作见于之前发的文章。感谢您的阅读！

工作中会遇到这样的情况，就是要计算某某员工在岗的时间，某某员工的真实年龄，或者某某合同签订多长时间了。等等和时间有关的问题，而且很多时候要求时间是要随着日期的变化自动更新的，今天的这篇，就是来给大家解决这些问题的。

第一种方法，使用datedif函数。函数的的写法是这样的=datedif(开始日期，结束日期，显示格式)。。第一个参数：出生日期。第二个参数：当前日期（today()）。第三个参数：年（“Y”）。。这种计算出来的是周岁。dateif函数是一个隐藏函数，所以只能自己手写进去，excel不会给予任何的提示。

第二种方法，一个比较笨但是更容易理解的方法，当前日期减去出生日期，除365天。最后一个生日是2001年1月1日的，因为计算方式不同，显示的是四舍五入之后的年龄。

如果保留两位小数，会发现年龄变成了带小数的数字了。。。两种方法就介绍到这里，如果对office办公软件的一些基本操作和小技巧感兴趣，请您关注我，我会不定期的分享一些工作中遇到的问题和解决办法给大家。

在我们平时的工作生活中经常会接触到身份证号，如果现在有很多人的身份证号，要统计这些人的出生日期，如何批量从很多人的身份证号中提取出来出生日期呢？下面我们以15个人的身份证号为例，批量提取他们的出生日期：

在C2单元格中输入公式：=TEXT(MID(B2,7,8),"0000-00-00")

这个公式的含义是，从B2单元格中的从左向右数第7个字符开始提取出来8个字符，因为身份证号中代表出生年月日的就是身份证号的第7位开始向右的8个数字。提取出来后放到TEXT文本函数中转换成特定格式的文本，这里的0000-00-00就是用横杠分隔取出的8个数字。

回车后：

这样就提取出来了B2单元格的出生年月日，然后向下填充公式即可。

这样所有人的身份证号中的出生年月日都被提取出来了。是不是很省事呢？

如果公式换成这样：=TEXT(MID(B2,7,8),"0000年00月00日") 会得出什么结果呢？请大家自己试试吧。

注意：

（1）以上方法提取出来的是文本格式的日期，如果要提取出来是数字格式的日期，可以在公式中的TEXT前面加上两个负号转换即可，例如：=--(TEXT(MID(B2,7,8),"0000-00-00"))

回车后：

然后将公式向下填充得到：

怎么全是数字？不要急，其实这些数字本质上是日期，选中这些数字，点击鼠标右键，选择【设置单元格格式】==》【数字】==》【日期】选择一种日期格式即可。

这样就现出日期的原形了。

（2）如果一个日期是数字格式，如下图中的A1单元格中是数字26639，如何转换成日期格式呢？只需在B1单元个中输入：=TEXT(A1,"yyyy年m月d日")

回车后：

这样就转换成日期格式了。

补充知识：

（1）Text函数：其功能是将数值转化为自己想要的文本格式

Text函数语法：

TEXT(value, format_text)

TEXT 函数语法具有下列参数：

value ：数值、计算结果为数值的公式，或对包含数值的单元格的引用。

format_text：用引号括起的文本字符串的数字格式。例如，“m/d/yyyy”或“#,##0.00”。

（2）MID函数：用于从字符串的指定位置开始，提取特定数目的字符串。

MID函数语法：

MID(text,start_num,num_chars)

MID函数语法具有下列参数：

text表示指定的字符串，一般为引用的单元格；

start_num表示指定位置 ，即表示要从字符串的由左向右哪一位开始。

num_chars表示指定个数，即要提取多少个字符。

MID(A1,5,3)就是从A1单元格的第5位开始，提取3个字符。

以上啰嗦了一大堆，不知道大家学到点知识没，反正我是学到了，哈哈！

excel不会函数也能身份证提取出生日期，这是怎么做到的呢？

总是看到别人用excel函数从身份证提取出生日期，我也想做到，可惜我不会excel函数啊，那可以做到吗？

可以的！

我们可以在需要出生日期的excel单元格输入第一个出生日期，比如19830317：

向下填充-快速填充-哇，excel瞬间得到结果：

现在的出生日期是19830317，如果我们想要的是1983-03-17这样的格式呢，我们首先想到更改为日期格式，可惜excel没有得到我们想要的结果，竟然是这样的：

那么怎么办呢？选择出生日期一列-数据-分列-固定宽度-日期-选YMD-确定，哈哈，完成：

怎么样，很简单吧，你学会了吗？快去试试吧！非常感谢您观看文本，如果你有更好的方法适用这个情况，请您留言分享！分享好玩、有趣、实用的手机和电脑技巧，您的支持就是我的最大动力！

如果平时我们留意的话，可以看到日期格式和时间格式有很多种。比如日期格式2017-5-30，也可以表示成2017年5月30日。同样的时间格式17:49，也可以表示成17时49分。有时我们根据不同的场合，需要不同的日期格式或时间格式。如果之前我们已经做好的日期和时间，能不能快速地变换成另一种格式呢？答案是可以的，我们就来一起了解一下是如何实现的。

日期格式变换

1、比如下面的表格，日期和时间形式如下，我们想把日期和时间变换成别的形式。目前的日期形式是2017-5-30

2、选择日期C2单元格，鼠标右键--设置单元格格式

3、在弹出的单元格格式对话框中，点击数字选项卡，在分类下选择日期， 类型下选择我们想要的形式，需要哪种类型选哪个类型就好了。

4、上面点击确定后，日期形式就变成新的一种形式：2017年5月30日

时间格式变换

1、同样的，我们也可变换时间格式，目前的时间形式是17:49

2、选择日期D2单元格，鼠标右键--设置单元格格式

3、在弹出的单元格格式对话框中，点击数字选项卡，在分类下选择时间， 类型下选择我们想要的形式，需要哪种类型选哪个类型就好了。

4、上面点击确定后，时间形式就变成新的一种形式：17时49分

总结：日期和时间格式变换主要用到单元格格式中数字选项卡，分别可以日期和时间的类型进行选择后变换，当我们需要对多个单元格变化时，只要全选相应的单元格就可以了。

本百家号：时代新生分享工作、生活、技能方面的经验，愿和您一起成长。请多多收藏或关注哦！

关于身份证号的操作，相信大家并不陌生，但是对于用到的函数，大家可能一直在犯糊涂，今天我们要学习的内容就是关于身份证号相关的函数……

一、提取出生年月。

方法：

1、选定目标单元格。

2、输入公式：=TEXT(MID(C3,7,8),"00-00-00")。

3、Ctrl+Enter填充。

备注：

如果对提取的出生年月没有格式要求，可以使用公式：=MID(c3,7,8）来实现。

二、自动填充性别。

方法：

1、选定目标单元格。

2、输入公式：=IF(MOD(MID(C3,17,1),2),"男","女")

3、Ctrl+Enter填充。

三、自动填充年龄。

方法：

1、选定目标单元格。

2、输入公式：=DATEDIF(D3,TODAY(),"y")。

3、Ctrl+Enter填充。

四、计算退休时间。

方法：

1、选定目标单元格。

2、输入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。

3、Ctrl+Enter填充。

4、设置单元格格式为：日期，并选择日期格式。

备注：

此处的计算方法是以男60岁，女50岁为退休原则进行计算的。如果男同志60岁，女同志55岁退休，男同志比女同志多工作60个月（5年），加上女性55年*12个月。则计算公式为：=EDATE(D3,MOD(MID(C3,17,1),2)*60+660)。

我们一般在这里学生档案以及员工的身份证信息时，在Excel输入身认证号以后，我们一般还得手动去输入身份证号码内的出生年月，这样无形中增加了我们的工作量，接下来小编就用一组模拟的数据，来演示如何快速的提取身份证号码中的出生年月：

提取方法一：使用函数公式提取，在C2单元格内输入以下公式，并向下填充即可。

=TEXT(MID(B2,7,8),"0-00-00")+0

注意：如果说你输入公式后，结果变成“30597”，那么我们就需要单元格的格式，如图：

解释：这个公式是有“MID函数”“TEXT函数”组成，我们现在身份证号码B2单元格中从它的第7位开始截取，截取的字符长度是8位，得到的计算结果就是“19831008”。然后在通过TEXT函数“TEXT(MID(B2,7,8),"0-00-00")”让它变成“1983-10-08”这样的格式。

经过这样的公式后，就有了日期的格式“1983-10-08”，但是这样的结果还不是真正的日期格式。经过“MID函数”“TEXT函数”等函数计算后得出的数值都是文本格式，一般情况下，不能参与后续的计算处理。

那么，在TEXT函数后面加零是什么意思呢？通常把这种形式叫做加零运算，通过运算，将文本格式的数值变成真正的日期格式，如果将加零换成1*（1乘以），也可以实现将文本格式向数值格式的转换的。

提取方法二：使用基础操作“数据分列”的方法来提取。

第一步：选择B列数据区域，单击【数据】【分列】，在弹出【文本分列向导 第1步，共3步】对话框中，选择“固定宽度”，单击下一步。

在【文本分列向导 第2步，共3步】对话框数据预览窗口中，单击第6位和第7位之间的刻度，再单击第14位和第15位之间的刻度（也就是留出出生日期的宽度），单击【下一步】。

在【文本分列向导 第3步，共3步】对话框中，依次点击最左侧列，点选【不导入此列（跳过）】。点击最右侧列，点选【不导入此列（跳过）】。点击中间列，点选【日期】。目标区域选择C2，单击完成。

这样，所有的出生日期就马上可以提取出来了。

学会了吗？这样是不是就减轻了你的工作压力~~

　　出身年月在excel中就是一个日期，日期在excel中实际上是一个数字，“1”为“1900/1/1”，往后1天，就增加1，即“2”为“1900/1/2”，所以，excel认可的日期，是可以参与运算的，excel中日期的格式有很多，而且可以自定义格式

　　默认情况下（单元格格式为设置时）：

　　输入“1977/1/12”（或“1977-1-12”），显示为"1977/1/12"；

　　输入“1977年1月12”则显示不变，仍为“1977年1月12”；

　　输入“1/12”（或者“1-12”），显示为“1月12日”；

　　输入“1977/1”（或“1977-1”），显示为“Jan-77”；

　　输入“77/1”，显示为“Jan-77”（excel识别日期为1977/1/1），这种情况比较特殊，从小开始说，输入“00/1”时，excel不会将其识别为日期，显示为文本“00/1”，然后是“01/1”……“31/1”，显示为“1月1日”……“1月31日”，从“32/1”开始，显示为“Jan-32”，也就是数字超过最大日后，开始识别为年，否则，识别为日。另外还有好几种情况，你感兴趣可以自己试验，这样的输入方式不规范，不建议使用。

　　另外再说一种人们常犯的错误，用“.”代替“/”，即“1977.1.12”样式，这种样式，excel认为是一个文本，显示为“1977.1.12”。

　　一般采用的输入方式为“1977/1/12”或者“1977-1-12”样式，显示样式，可以通过改变单元格格式来实现，比如“1977年1月12日”、“1977/01/12”、“1977年1月”、“1月12日”等等，方法是：

　　选中输入日期后的单元格，鼠标右键，设置单元格格式，“数字”选项卡，日期，选一个自己喜欢的样式，如果没有，就选最下边的自定义，自定义一个，如“yyyy年mm月”显示为“1977年01月”，格式中的“y”为“年”，“m”为“月”，“d”为“日”，年的定义格式为“yyyy”或者“yy”，分别显示4位年份和2位年份，“yyyy”还可以用“e”代替。“mm”、“dd”这种将格式双写，意思是当其不足2位时，用“0”补足。

（本文内容由百度知道网友第七次要分贡献）

一个表格中有一列人员的出生年月日，最笨的方法我们就是一条一条的算出来，如果只有几条，那还好说，很简单的就搞定了，要是这一列数据有上百，上千设置上万条数据呢？你还要逐条算吗？ 下面告诉你的快速简便的计算方法。

这里我们用到一个函数是today函数

具体公式为=(today()-B2)/365 公式中的B2即为表格中出生日期那一列，实际根据自己的表格来。

将此公式输入到年龄列中，回车即可计算出年龄

特别注意：有的人在输入公式后，可能会出现如下情况，会以为难道是公式不对吗？

放心，公式是对的，只要简单的操作一下就可以，选择单元格，右键-设置单元格格式-选择数值，将小数位数改为0，单击确定即可。

日期是Excel中常见的一种数据格式，在设置日期格式时常常会遇到一些问题导致日期格式设置不成功。本文将详细介绍几种日期设置不成功的情况及相关的解决方法。

设置日期格式，却显示“#”

Excel中默认的单元格格式为“常规”，在常规模式下输入不含分隔符的日期（例如20180116），此时再设置成“日期”格式，就会变成“######”。

日期前带有一个绿色小三角

有两种情况会导致这个问题的出现：第一：有时候不小心将单元格的格式设置为“文本”，然后输入不含分隔符的日期，就会看到日期前显示一个绿色小三角。接着再设置成“日期”，却怎么也设置不成功。第二：用一些公式去提取日期，例如使用MID公式从身份证号码中提取出来的出生日期，日期先就会显示绿色小三角。

问题怎么解决呢？

出现了上面两种情况，直接设置成日期当然不会成功。可以采用“分列”的方法来解决。首先选中数据，点击“数据”——“分列”——“下一步”——“下一步”——选择格式为“日期”——“完成”——这就能成功设置成日期啦。

也可以使用DATE公式解决，DATE公式格式为“DATE(Year, Month, Date)”，用于提取日期，公式使用前单元格的格式为常规，使用后，也会自动将单元格的格式设置成“日期”。如下图，输入“=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))”，接着复制公式结果，选择性粘贴为“数值”，最后再设置成“日期”即可。

输入日期，却变成了数字

有两种情况会导致这种错误的出现，之前已经分析过了，这里不再赘述，请参考Excel日期变数字怎么回事。

上一篇：Excel数字变日期怎么回事

来源：解晴新生，点击文章开头的“解晴新生”或“关注”可以查看更多相关文章哦。方便的话，请点赞支持下，谢谢！