excel中判断身份证号码的出生年月是否正确合法

Question

在B1列输入身份证号码，要判断第7—-12位，或7-14位的年份不能超过100年，月份大于1月小于12月，日期大于01号小于31号，润月小于28号的公式。用条件格式以不同颜色显示出来！或其它方式！请高人指教！
楼上所说的只能判断身份证号码是否是15或18位。这个很好实现。我想实现的是判断日期是否合法！！！

Answer 1

用一个单元格判断所有情况公式长度将会超过限制。
建议添加4个辅助列（假定为JKLM）来判断，J列用来判断18位身份证日期是否合法，公式如下：
=IF(AND(LEN(B1)<>18,LEN(B1)<>15),"身份证位数不对",IF(LEN(B1)=18,IF(OR(YEAR(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,7,4)),MONTH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,13,2))),"日期有误",0),0))
K列用来判断15位身份证日期是否合法：
=IF(AND(LEN(B1)<>18,LEN(B1)<>15),"身份证位数不对",IF(LEN(B1)=15,IF(OR(YEAR(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,7,2))+1900,MONTH(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,11,2))),"日期有误",0),0))
L列用来判断日期是否超出范围：
=IF(AND(LEN(B1)<>18,LEN(B1)<>15),"身份证位数不对",IF(LEN(B1)=18,IF(OR(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2)))<DATE(YEAR(TODAY())-100,MONTH(TODAY()),DAY(TODAY())),DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2)))>TODAY()),"日期超范围",0),IF(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2)))<DATE(YEAR(TODAY())-100,MONTH(TODAY()),DAY(TODAY())),"日期超范围",0)))
M列综合前3列的情况，得到结果：
=IF(OR(J1:L1="身份证位数不对"),"身份证位数不对",IF(OR(J1:L1="日期有误"),"日期有误",IF(L1="日期超范围","日期超范围","")))
公式以Ctrl+Shift+Enter三键结束。

最后你再在B列设定用条件格式根据M列的情况给出不同的提示:
用鼠标选定B列,点"格式"->"条件格式",将条件1设为:
公式=M1="身份证位数不对"
点"格式"->"字体"->"颜色",选中红色,点"确定"。
点"添加",将条件2设为:
公式=M1="日期有误"
点"格式"->"字体"->"颜色",选中绿色,点"确定"。
点"添加",将条件3设为:
公式=M1="日期超范围"
点"格式"->"字体"->"颜色",选中蓝色,点两次"确定"。
这样B列字体为红色的表示"身份证位数不对",绿色的表示"日期有误",蓝色的表示"日期超范围"。

注：将几个辅助列的公式向下复制后，可将该几列隐藏。

Answer 2

楼上所说的只能判断身份证号码是否是15或18位。这个很好实现。如何实现判断日期是否合法！！！比如出生日期为1734年13月35号，年份太大，能活到2007年都成老妖精了。我们的日期中有13月35号吗？用什么办法？？？

Answer 3

用mid函数就可以。
MID(数据,开始位数,结束位数)
比如=MID(A1,7,4)返回的就是a1中身份证号的年。
用条件格式（格式菜单下），选择公式并按你的需要设置，就可以实现不同颜色显示。这个可以参考 http://tech.sina.com.cn/s/s/2004-12-13/0903475051.shtml

Answer 4

： =IF(OR(J1:L1="身份证位数不对"),"身份证位数不对",IF(OR(J1:L1="日期有误"),"日期有误",IF(L1="日期超范围","日期超范围",""))) 公式以Ctrl Shift Enter三键结束。 最后你再在B列设定用条件格式根据M列的情况给出不同的提示。