第一篇：EXCEL中根據(jù)身份證號計算出生日期、年齡、性別、月份間隔

根據(jù)身份證號碼計算出生日期、年齡、性別。其中D4單元格中輸入身份證號碼 1.計算出生日期： 出生日期為（年月日）

=IF(LEN(D4)=15,“19”&MID(D4,7,2)&“年”&MID(D4,9,2)&“月”&MID(D4,11,2)&“日”,MID(D4,7,4)&“年”&MID(D4,11,2)&“月”&MID(D4,13,2)&“日”)出生日期為（Y-M-D）

=MID(D4,7,4)&“-”&MID(D4,11,2)&“-”&MID(D4,13,2)

2.計算年齡：

=YEAR(TODAY())-MID(D4,7,4)

3.計算性別：

=IF(MOD(IF(LEN(D4)=15,MID(D4,15,1),MID(D4,17,1)),2)=1,“男”,“女”)

4.根據(jù)兩個年月日算間隔多少月：

=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

5.數(shù)值保留兩位小數(shù)：

=ROUND(A2,2)

第二篇：利用身份證號自動提取出生日期和年齡性別

在excel表格中，根據(jù)身份證號碼就可以自動提取出生年月、性別、年齡。

第一，身份證號碼的組成。

當(dāng)今的身份證號碼由18位數(shù)字組成：前6位為地址碼，第7至14位為出生日期碼，第15至17位為順序碼，第18位為校驗碼。

15~17位為順序號，其中第17位如果是單數(shù)為男性分配碼，雙數(shù)為女性分配碼。即通過第17位數(shù)字，可以判斷性別，奇數(shù)為男，偶數(shù)為女。

下面以實例的方式講解如何根據(jù)身份證號碼自動提取出生年月、性別、年齡。首先，在excel中輸入以下表格，實例如下面所示。

A

B

C

D

E

姓名

身份證號碼

出生日期

性別

年齡

張三

***786

1972年05月03日

女

第二，根據(jù)身份證號碼自動提取出生年月

在目標(biāo)單元格輸入公式：

=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&“日”，回車確定，單元格內(nèi)會出現(xiàn)與身份證號碼相關(guān)的出生日期。其它單元格的填充，不必一一輸入公式，只需選中該單元格復(fù)制，在其它單元格內(nèi)粘貼即可。

提示：MID函數(shù)是提供的一個“從字符串中提取部分字符”的函數(shù)命令。

第三，根據(jù)身份證號碼自動提取性別的方法

在目標(biāo)單元格輸入公式：

=IF(MOD(RIGHT(LEFT(B2,17)),2),“男”,“女”)

提示：IF函數(shù)：根據(jù)邏輯表達式測試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。

RIGHT函數(shù)：根據(jù)所指定的字符數(shù)返回文本串中最后一個或多個字符。

第四，根據(jù)身份證號碼自動提取年齡的方法

在目標(biāo)單元格輸入公式：=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),“#-00-00”),NOW(),“y”)，回車確定。

第三篇：excel中由身份證號求出生日期、性別、年齡

在EXCEL2003中把數(shù)字轉(zhuǎn)換成日期

1。假定A2為文本數(shù)字，B2存放轉(zhuǎn)換后的日期。請在B2輸入公式：

=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))

=DATE(MID(H2,1,4),MID(H2,5,2),MID(H2,7,2))

2。把B2設(shè)置成你要的格式。右擊B2單元格-----選“設(shè)置單元格格式”----選“數(shù)字”下的“自定義”-----在“類型”框中輸入 “YYYY-MM-DD-”

（引號不要輸入）-----點“確定”。

格式為“**年**月”的：

設(shè)A列是身份證號，可在B1輸入：

=IF(LEN(A1)=15,MID(A1,7,2)&“年”&MID(A1,9,2)&“月”,MID(A1,9,2)&“年”&MID(A1,11,2)&“月”)

再將公式向下復(fù)制即可。

格式為“****年**月”的：

=IF(LEN(A1)=15,“19”&MID(A1,7,2)&“年”&MID(A1,9,2)&“月

”,“19”&MID(A1,9,2)&“年”&MID(A1,11,2)&“月”)

再將公式向下復(fù)制即可。

如果你再想限制身份證號輸入錯誤，可用這個公式：

=IF(OR(LEN(A1)=15,LEN(A1)=18),IF(LEN(A1)=15,MID(A1,7,2)&“年

”&MID(A1,9,2)&“月”,MID(A1,9,2)&“年”&MID(A1,11,2)&“月”),“證號錯誤”)這樣只要輸入的證號不是15位或18位就提示了。

判別性別。

這個公式是先對存放身份證的單元格A1進行判斷是15位號還是18的號，然后再對性別代碼進行奇偶性判斷。

=IF(LEN(A1)=15,IF(MOD(RIGHT(A1,1),2)=1,“男”,“女

”),IF(MOD(MID(A1,17,1),2)=1,“男”,“女”))

（1）根據(jù)身份證號碼求性別 ：

=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)

（2）根據(jù)身份證號碼求出生日期：

=CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)

（3）根據(jù)身份證號碼求出年齡：

=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周歲”)

或=DATEDIF(D3,TODAY(),“y”)

1、身份證號碼相關(guān)知識

在了解如何實現(xiàn)自動從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當(dāng)今的身份證號碼有15/18位之分。早期簽發(fā)的身份證號碼是15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴展（由兩位變?yōu)樗奈唬┖湍┪布恿诵灤a，就成了18位。這兩種身份證號碼將在相當(dāng)長的一段時期內(nèi)共存。兩種身份證號碼的含義如下：

（1）15位的身份證號碼：1~6位為地區(qū)代碼，7~8位為出生年份(2位)，9~10位為出生月份，11~12位為出生日期，第13~15位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。

（2）18位的身份證號碼：1~6位為地區(qū)代碼，7~10位為出生年份(4位)，11~12位為出生月份，13~14位為出生日期，第15~17位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。18位為效驗位。

2、應(yīng)用函數(shù)

在此例中為了實現(xiàn)數(shù)據(jù)的自動提取，應(yīng)用了如下幾個Excel函數(shù)。

（1）IF函數(shù)：根據(jù)邏輯表達式測試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。

語法形式為：IF（logical_test, value_if_true,value_if_false）

（2）CONCATENATE：將若干個文字項合并至一個文字項中。

語法形式為：CONCATENATE(text1,text2??)

（3）MID：從文本字符串中指定的起始位置起，返回指定長度的字符。語法形式為：MID(text,start_num,num_chars)

（4）TODAY：返回計算機系統(tǒng)內(nèi)部的當(dāng)前日期。

語法形式為：TODAY（）

（5）DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。

語法形式為：DATEDIF(start_date,end_date,unit)

（6）VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。

語法形式為：VALUE(text)

（7）RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個或多個字符。語法形式為：RIGHT(text,num_chars)

（8）INT：返回實數(shù)舍入后的整數(shù)值。語法形式為：INT(number)

4、公式寫法及解釋（以員工Andy為例說明）

說明：為避免公式中過多的嵌套，這里的身份證號碼限定為15位的。如果您看懂了公式的話，可以進行簡單的修改即可適用于18位的身份證號碼，甚至可適用于15、18兩者并存的情況。

（1）根據(jù)身份證號碼求性別

=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)

公式解釋：a.RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字，實際求得的為代表數(shù)字的字符串

b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字 c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判斷這個身份證號碼是奇數(shù)還是偶數(shù)，當(dāng)然你也可以用Mod函數(shù)來做出判斷。

d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判斷出這個號碼是偶數(shù)時，顯示“女”，否則，這個號碼是奇數(shù)的話，則返回“男”。

（2）根據(jù)身份證號碼求出生日期

=CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解釋：a.MID(E4,7,2)為在身份證號碼中獲取表示年份的數(shù)字的字符串 b.MID(E4,9,2)為在身份證號碼中獲取表示月份的數(shù)字的字符串

c.MID(E4,11,2)為在身份證號碼中獲取表示日期的數(shù)字的字符串

d.CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))目的就是將多個字符串合并在一起顯示。

（3）根據(jù)參加工作時間求年資（即工齡）

=CONCATENATE(DATEDIF(F4,TODAY(),“y”),“年

”,DATEDIF(F4,TODAY(),“ym”),“個月”)

公式解釋：

a.TODAY()用于求出系統(tǒng)當(dāng)前的時間

b.DATEDIF(F4,TODAY(),“y”)用于計算當(dāng)前系統(tǒng)時間與參加工作時間相差的年份

c.DATEDIF(F4,TODAY(),“ym”)用于計算當(dāng)前系統(tǒng)時間與參加工作時間相差的月份，忽略日期中的日和年。

d.=CONCATENATE(D

第四篇：Excel中根據(jù)身份證號碼自動填出生日期、性別、年齡

Excel中根據(jù)身份證號碼自動填出生日期、性別、計算年齡

出生日期：F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))自動錄入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,“女”,“男”)計算年齡：=IF(LEN(B2)=15,109-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2009-VALUE(MID(B2,7,4)),“身份證錯”))

說明：VALUE將一個文本字符串轉(zhuǎn)換成數(shù)值，LEN返回文本字符串中的字符個數(shù)，MID從文本字符串中指定的起始位置起返回指定長度的字符，MID(文本,開始字符,所取字符數(shù))。109表示當(dāng)前日期為2009年，如果是2010年則改為110，2009表示當(dāng)前日期為2009年。DATE代表日期的數(shù)字，MIDB自文字的指定起始位置起提取指定長度的字符串