我司有三名技术宅兼单身狗，分别是陈晓皮，陆之涵，姜度华三人各具特色，小皮嫣然是一个投错胎的人，一个男子汉身躯的却装了个小女生的灵魂,他解释说：什么叫娘儿，奴家就是将把生活过的精致，这叫有品位，而陆之涵很好的诠释了什么叫一个大嘴毁掉整张脸，他也有词：什么叫有福，像我这样就有福啊，能吃是福吗！还有姜度华挺俊俏的脸，硬生生让身高把颜值拉倒平均分以下，而他说：什么叫精华，我浑身上下都是干货，没有多余一点废料啊！

除此之外他们仨自封Excel高手，小皮自说知晓Excel一切技巧的人，小陆自封函数仓库，没有他不知道的函数，而小度则觉得数学简化优化公式的王道，他们还有个自己的封号“办公三剑客”！今天的内容要从几个月前单身女同事愁眉苦脸的从老板办公室出来说起：原来让她把在职的员工排查是否有退休的人员？(注:原表只有两列数据，A列姓名，B列身份证号，数据共1100多条)。

霉霉第一找到的人就是小皮，问：哥，有什么技巧能帮我们搞定统计这些数据啊？

小皮：小意思儿，给我10分钟，我帮你搞定！于是就开始他的骚操作：

首先，ctrl + g，调出定位窗口，然后选择【定位条件】按钮，弹出定位条件窗口，选择常量，然后点确定就选择好了非空白的内容。然后ctrl + t,弹出套用表格式窗口，点确定，将选取内容转化成智能表格。

快速选择有内容的单元格

其次，分别创建出生年，出生月，出生日辅和性别辅助列并设置为文本格式，分别对应身份证分别输入出生年（7-10位），月（11-12位），日（13-14位），性别（16-17位数字）输入完成后，分别ctrl+e，完成其所有内容的填充，然后在最后填充公式=IF(TODAY()>DATE([@出生年份]+IF(MOD([@性别参考],2),60,55),[@出生月份],[@出生日]),”退休”,””)，然后选中姓名列，按条件格式，选等于“退休”，设置个显眼的颜色，搞定！最后选中辅助列，ctrl+0隐藏就行了！

小皮回头吓一跳，除了霉霉崇拜目光外，还多了两个人，哪就是小陆和小度。

小陆迫不及待的开口说：你这不行啊，严谨性判断都没有失败，辅助列太多失败，修改数据需重新操作失败！让我来教教你这个公式该怎么写。对霉霉说：我2分钟帮你搞定！

小陆的操作也很流畅，说时迟那是加快，将刚才能复制到新表，新建是否退休列，然后就输入公式=IFERROR(IF(TODAY()>EDATE(MID([@身份证],7,4)&”/”&MID([@身份证],11,2)&”/”&MID([@身份证],13,2),IF(MOD(MID([@身份证],17,1),2),60,55)*12),”退休”,””),”异常”)，然后拖拽公式，完事！

小度终于开口了：你公式写的不错，却不是最优解，失败！我来帮你修的完美写吧：说着推开了小陆，把公式改成：=IFERROR(IF(TODAY()>EDATE(MID([@身份证],7,4)&”/”&MID([@身份证],11,2)&”/”&MID([@身份证],13,2),(55+MOD(MID([@身份证],17,1),2)*5)*12),”退休”,””),”异常”)

小皮看完他俩的操作说：公式没有毛病，你们这样的写完公式，还得拖拽填充才能完成，不用智能表格，太失败！小陆和小度脸也红了，不过霉霉只顾看表格，却没有发现，看完没有问题，迅速把文件保存，然后给老板发过去，回过头一脸崇拜的说：我拜你们为师吧！

他三异口同声说：你想学什么啊？

霉霉：公式吧，我看皮老师好多的步骤，姜老师一个公式搞定了！

小皮：哪没有我什么事了，我赶紧去工作了！

小度酸溜溜的说：公式虽好，没有数学基础，也不哪么容易学会的！

霉霉迷茫的看着小姜，小姜说：公式并非一天能学成，不过今天只说关于日期的两个函数，还是可以很好掌握的！

霉霉拼命点头，小陆就开始关于日期DATE和EDATE的讲解；

DATE函数

日期函数中比较常用到的函数之一，它的能力就是把三个参数转化成日期，具体的语法结构如下：

date函数的使用语法结构图

注：遇到错误的日期格式会报错#NUM!

EDATE函数

使用的场景挺多的函数之一，它的功能是可以范围几个月之后的日期，比如：1929/2/23的5个月之后的日期为=EDATE(“1929/2/23”,5)=1929/7/23,它的语法结构:

EDATE函数使用语法结构示意图

​注：遇到错误会返回#VALUE!。

这两个就是我们需要筛选人员的原型函数，第一个小皮使用的原理的，通过出生的年份+60（男）/55（女），通过date来的他们退休的准确日期，再与today函数返回的日期比较，>返回的日期的说明还没有退休，<=的就已经退休啦。

霉霉：什么today函数？它有什么作用啊？

小陆：today能力是返回今天日期，比如今天是2019年4月3日，输入=today()回车，返回2019/4/3,它也是Excel函数少数的没有参数的函数的，而我用的EDATE函数原理差不多，只不过我通过函数截取身份证的中的年月日，然后通过性别识别码计算出他们是55周岁退休还是60岁，再乘上月份并与今天的日期的比较。

这时小度说：最妙的应该我的数学优化公式了，真是点睛之笔，你看他用IF(MOD(MID([@身份证],17,1),2),60,55)*12，我只用一个数学式子不用判断只用算出来：(55+MOD(MID([@身份证],17,1),2)*5)*12；

小陆说：你牛你牛！你不是工作了吗？忙完了！

小度：哼，我倒水去！

小皮也凑过来说：你们俩公式写的好又怎么样，都只会傻操作，你看我的智能表格，只需填一个公式，下面的自动补充，修改公式自动更新，哪像你们这样，每次修改公式还得一点点拖拽，累啊！

霉霉听他们这说的乐开花了，心想:我是找对了，以后表格就靠他三儿啦。

最后说：哪天我请你们吃饭，谢谢你们帮我这么大忙!

这三人一个客气的都么有，纷纷说自己知道什么餐厅好吃……，自此之后，办公室就成了他三向霉霉献媚的地方。

好了，今天的故事就到这了，希望你从中有所收获，也希望你对上面提到的公式中有什么不明白的可以下面留言或私信我，看到后第一时间回复，最后附上一张身份证信息详解图，希望对你有所帮助！

身份证号段详解示意图