帮助excel函数问题，多谢

Question

如何从在a1输入身份证号后，自动在b1,c1，d1分别写出性别、出生年月日（2013.01.20格式）和填写当时的周岁岁数？多谢

Answer 1

A1：18位身份证号码
B1：性别，=IF(MOD(MID(A1,17,1),2)=0,"女","男")
C1：出生年月日，=MID(A1,7,4)&"."&MID(A1,11,2)&"."&MID(A1,13,2)
D1：目前(到当前这一天)周岁年龄，=ROUNDDOWN(YEARFRAC(TEXT(MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),"yyyy-mm-dd"),TODAY()),0)

Answer 2

性别：
=IF(A1<>"",IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女"),)
年龄：
=IF(A1<>"",DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y"),)
出生年月日：
=IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0,)

Answer 3

B1公式
=if(mod(mid(a1,15,3),2),"男","女")

C1公式
=text(mid(a1,7,len(a1)/2-1),"0!.00!.00")

D1公式
=datedif(text(mid(a1,7,len(a1)/2-1),"0-00-00"),today(),"Y")

Answer 4

A1为身份证号
B1公式
=IF(MOD(MID(A1,17,1),2),"男","女")
C1公式
=MID(A1,7,4)&"."&MID(A1,11,2)&"."&MID(A1,13,2)
D1公式
=DATEDIF(TEXT(SUBSTITUTE(C1,".","-"),"yyyy-m-d"),NOW(),"y")+1追问

年龄公式不对呀，能否用计算机系统时间去减出生年月日算岁数？

追答

Now()就是计算机系统时间
公式后面有个+1，根据需要自行取舍吧

Answer 5

姓名性别出生时间年龄身份证号彭红女1979-07-0825371082197907087126李存贵男1994-12-0510533527199412051838

B CDE F 
C=IF(MOD(MID(F3,15,3),2),"男","女"); D=MID(F3,7,4)&"-"&MID(F3,11,2)&"-"&MID(F3,13,2); E==YEAR(TODAY())-MID(F3,7,4)来自：求助得到的回答

Answer 6

b1公式: =IF(LEN(A1)=18,IF(MOD(MID(A1,17,1),2),"男","女"),"")
c1公式: =IF(LEN(A1)=18,TEXT(--TEXT(MID(A1,7,8),"0-00-00"),"e.mm.dd"),"")
d1公式: =IF(LEN(A1)=18,DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),TODAY(),"y"),"")