在工作中，有时会让其他人在Excel表格中填写一些日期信息，而在对搜集到的报表日期进行汇总时，可能会因为填表人的习惯不同，导致填写的日期格式也不相同，在复制粘贴后会让汇总后的日期列表显得杂乱无章，影响美观。

下面以Excel2010为例，介绍一个快速修正表格中不规范日期的小技巧，让表格中的日期格式保持一致。

按键盘的“Ctrl+H”组合键，或者点击Excel“开始”选项卡“查找和选择”中的“替换”。

在替换界面的“查找内容”处输入日期格式中不规范的间隔符号,如“.”在替换为处输入“/”。

输入完成后，点击“全部替换”按钮。

此时Excel会自动将不规范的间隔符号替换成统一的“/”符号，并且弹出替换完成提示。

这样，表格中的日期格式就统一了，但下图表格因为表示月和日的数字位数不一致，有1位的、还有两位的，所以看起来还是不太整齐。而且年月日的间隔符号是“/”，不太符合我们的习惯。下面再介绍一下怎么把表示月和日的数字位数统一成2位，把年月日的间隔符号换成“-”等符号。

先框选所有包含日期的单元格，然后在选择范围上点击鼠标右键，弹出右键菜单后，再点击菜单中的“设置单元格格式”。

弹出“设置单元格格式”对话框后，点击数字选项卡中左侧的“自定义”。

在右侧的“类型”处输入“yyyy-mm-dd”或者“yyyy.mm.dd”，其中的“-”和“.”表示用该符号做为年月日间的间隔符号，也可以换成其他的符号。

输入后点击“确定”按钮。

这样，日期列表看起来就规整了。

我们在用Excel给公司员工记录详细信息的时候，往往需要输出身份证号码、性别和出生年月等消息，一个一个的输入很是麻烦，有什么快速输入的方法呢？今天我们就一起学习一下吧。

提取性别：

首先我们看下面的表格是员工的姓名和身份证号码。

接下来我们需要输入性别和年龄，怎么快速输入性别呢？我们在性别列输入=IF(MOD(MID(K3,17,1),2),"男","女")

就可以了，回车后性别就自动提取出来了。

提取出第一个性别后，我们可以按住第一个单元格的右下角。拖住不放拉到最后，就快速的把所有人的性别信息提取出来了。非常方便。

需要注意的是由于我的身份证号码位置在K3，所以在上面使用了K3，大家可以找到自己对应的列来修改，不要直接复制，可能会产生错误。后面的17是指找到身份证号的17位用来确定男女，1是男，2是女，这个是我们国家用来区分男女的规则。

提取出生年月：

和刚才提取性别一样，我们在出生年月列输入公式 =MID(k3,7,4)&"年"&MID(k3,11,2)&"月"&MID(k3,13,2)&"日" 输入后，出生年月就自动提取出来了。

这里的K3依旧是我身份证号码所在位置，大家把K3修改成你表格中身份证号码所在位置就可以，其余的都不用修改，这个公式的意思是：MID函数作用为从指定的字符串中截取出指定数量字符，我们的公式中的“MID(K3,7,4)”,第一个参数表示K3单元格中的身份证号的所在列，7表示从第7位开始计算，4表示从第7位开始共截取几位数字.年份是4位，所以我们写4。我们可以刚刚才一样的操作，按住单元格右下角拉到最后，所有人的出生年月就提取出来了。

如果你需要 2012-08-08 这样格式的出生年月，可以用公式 =TEXT(MID(K3,7,8),"0000-00-00")

提取年龄：

出生年月和性别提取结束以后，就剩下提取年龄了。和上两个相同，我们需要在年龄所在列输入公式

=YEAR(NOW())-IF(LEN(K3)=18,MID(K3,7,4),19&MID(K3,7,2))

这个公式里，IF(LEN(K3)=18的意思是，如果这个号码是18位，当然，现在15位的身份证可能已经没有了，保险起见，大家可以加上这个用来以防万一。其余的就不用讲解了，基本上和刚才的差不多。

注意事项：

需要提醒大家注意的是，上面的所有公式中K3的出现是因为我的身份证号码所在的位置是表格的K3。大家在复制公式的时候，只需要修改K3就可以了，其余的可以不变。

其实，公式在Excel中非常重要，利用身份证号也可以提取很多东西，例如生肖、星座等等，由于使用的并不是很多，所以就不在这里写了。希望水煮鱼科技的这个文章可以帮助到你。

身份证是居民的身份象征，在各个领域都要用到身份证。身份证中可以提取许多的相关信息，然而大多数人都不知道如何应用身份证号来提取有用信息。下面，小编教大家用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提取出生日期、性别、年龄、及户口所在地等信息的具体方法，视频操作见于之前发的文章。感谢您的阅读！

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

一、提取出生年月。

方法：

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)。

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

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

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

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

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

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

如果平时我们留意的话，可以看到日期格式和时间格式有很多种。比如日期格式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分

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

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

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

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

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

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

　　出身年月在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”补足。

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

Excel表格中根据身份证号码自动填出生日期、性别、年龄

右击所在列→“设置单元格格式”为“常规” →复制以下（蓝色）公式：

=================================================================

◎ A1为15位或18位身份证号，要在B1算出 生日

=TEXT(19*(LEN(A1)=15)&M")

◎ A1为15位或18位身份证号，要在B1算出 性别

=IF(MOD(MID(A1,15+(LEN(A1)=18)*2,1),2),"男","女")

◎ A1为15位或18位身份证号，要在B1算出 截止到“今天”的 年龄

=DATEDIF(TEXT((LEN(A1)=15)*19&M"),TODAY(),"Y")

注意：根据表格修改单元格“(A1)”下图已经修改为“(B1)”。修改方式：将上述公式打入后，抹黑上述公式中的“A1”（不要抹括号）,再点“B1”单元格，公式中就自动生成“B1”。然后下拉“C1”，以下所有行就全部自动完成了。

---另：

根据身份证号码求出生日期、性别、年龄、15-18位转换

复制以下蓝色代码：

根据身份证计算年龄

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

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

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

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

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

问题怎么解决呢？

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

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

输入日期，却变成了数字

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

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

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