D2單元格出生日期計算公式: =IF(C2="","",VALUE(MID(C2,7,4)&"/"&MID(C2,11,2)&"/"&MID(C2,13,2))) E2單元格年齡計算公式: =IF(C2="","",YEAR(TODAY())-MID(C2,7,4)) F2單元格籍貫計算公式: =IF(C2="","",LOOKUP(VALUE(LEFT(C2,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"內(nèi)蒙古自治區(qū)";21,"遼寧省";22,"吉林省";23,"黑龍江省";31,"上海市";32,"江蘇省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山東省";41,"河南省";42,"湖北省";43,"湖南省";44,"廣東省";45,"廣西壯族自治區(qū)";46,"海南省";50,"重慶市";51,"四川省";52,"貴州省";53,"云南省";54,"西藏自治區(qū)";61,"陜西省";62,"甘肅省";63,"青海省";64,"寧夏回族自治區(qū)";65,"新疆維吾爾自治區(qū)";71,"臺灣省";81,"香港特別行政區(qū)";82,"澳門特別行政區(qū)";"","0"})) G2單元格性別計算公式: =IF(MOD(MID(C2,17,1),2)=0,"女","男") H2單元格工齡計算公式: =DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"天" |
|