excel中如何设置身份证年龄超限显示

Question

在excel中输入身份证后在输入身份证这一列里面，自动标记出超出20周岁-49周岁的，比如单元格显示为大红色什么的。

Answer 1

楼上不要着急赚金币而忽略了答案的质量，请看清人家的问题
1、首先要确保单元格是文本格式再输入身份证，不然可能导致输入后身份证后三位变为零；
2、在需要输入身份证的区域设置条件格式—新建规则—使用公式确定要设置格式的单元格，在下面的输入框中输入以下公式，并设置一个你想要的格式，如设置为字体红色：
=SUMPRODUCT(N(DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),NOW(),"y")>={20,50}))=1
（设置条件格式的时候尽量不要选择整列，会使你的表格体积变大并且运算变慢，公式中的A1是你选中的区域的第一个单元格，可以根据自己的需要修改）

追问

我输入后提示“在条件格式条件中不能使用联合、交叉引用或数组常量”，小于20周岁，大于49周岁的

追答

抱歉，可以先按ctrl + F3，打开名称管理器，并新建一个名称

 

然后把条件格式公式改为：

=SUMPRODUCT(N(DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),NOW(),"y")>=age))=1

Answer 2

用条件格式，选中A列，开始－－样式－－条件格式，新建规则，用公式：
=AND(DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),TODAY(),"y")>=20,DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),TODAY(),"y")<=49)
设置格式：填充红色。

追问

条件刚才没有说清楚，是小于20周岁，大于49周岁的，自动标记红色

追答

公式修改一下：
=OR(DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),TODAY(),"y")49)

追问

您这个时间是具体到月日然后跟系统时间是相对的吧？

追答

是的，从身份证中提取的生日（年月日），与系统日期进行比较，得到的“周岁”。

Answer 3

你要标记的年龄到底是大于20岁，还是大于49岁，还是小于20岁且大于49岁。

追答

我想你要标记的是大于等于20岁且小于等于49岁的。看图片。

例图中，我在D6单元格里输入了身份证号并将单元格格式设置成文本，然后如图1设置条件格式——公式——格式，完成。具体公式我把它复制粘贴到图2的D4单元格里了，你看一下。