資源描述:
《excel日期函數(shù):做人事管理日期計(jì)算》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、excel日期函數(shù):做人事管理日期計(jì)算示例示例:做一個(gè)美觀簡潔的人事資料分析表1、示例說明在如圖6所示的某公司人事資料表中,除了編號、員工姓名、身份證號碼以及參加工作時(shí)間為手工添入外,其余各項(xiàng)均為用函數(shù)計(jì)算所得。圖6在此例中我們將詳細(xì)說明如何通過函數(shù)求出:(1)自動(dòng)從身份證號碼中提取出生年月、性別信息。(2)自動(dòng)從參加工作時(shí)間中提取工齡信息。2、身份證號碼相關(guān)知識(shí)在了解如何實(shí)現(xiàn)自動(dòng)從身份證號碼中提取出生年月、性別信息之前,首先需要了解身份證號碼所代表的含義。我們知道,當(dāng)今的身份證號碼有15/18位之分
2、。早期簽發(fā)的身份證號碼是15位的,現(xiàn)在簽發(fā)的身份證由于年份的擴(kuò)展(由兩位變?yōu)樗奈唬┖湍┪布恿诵?yàn)碼,就成了18位。這兩種身份證號碼將在相當(dāng)長的一段時(shí)期內(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位為順
3、序號,并能夠判斷性別,奇數(shù)為男,偶數(shù)為女。18位為效驗(yàn)位。3、應(yīng)用函數(shù)在此例中為了實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提取,應(yīng)用了如下幾個(gè)Excel函數(shù)。(1)IF函數(shù):根據(jù)邏輯表達(dá)式測試的結(jié)果,返回相應(yīng)的值。IF函數(shù)允許嵌套。語法形式為:IF(logical_test,value_if_true,value_if_false)(2)CONCATENATE:將若干個(gè)文字項(xiàng)合并至一個(gè)文字項(xiàng)中。語法形式為:CONCATENATE(text1,text2……)(3)MID:從文本字符串中指定的起始位置起,返回指定長度的字符。語法
4、形式為:MID(text,start_num,num_chars)(4)TODAY:返回計(jì)算機(jī)系統(tǒng)內(nèi)部的當(dāng)前日期。語法形式為:TODAY()(5)DATEDIF:計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。語法形式為:DATEDIF(start_date,end_date,unit)(6)VALUE:將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。語法形式為:VALUE(text)(7)RIGHT:根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。語法形式為:RIGHT(text,num_chars)(8)INT:返回實(shí)數(shù)舍入
5、后的整數(shù)值。語法形式為:INT(number)4、公式寫法及解釋(以員工Andy為例說明)說明:為避免公式中過多的嵌套,這里的身份證號碼限定為15位的。如果您看懂了公式的話,可以進(jìn)行簡單的修改即可適用于18位的身份證號碼,甚至可適用于15、18兩者并存的情況。(1)根據(jù)身份證號碼求性別=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")公式解釋:a.RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字,實(shí)際求得的為代表數(shù)字的字符串b
6、.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判斷這個(gè)身份證號碼是奇數(shù)還是偶數(shù),當(dāng)然你也可以用Mod函數(shù)來做出判斷。d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")及如果上述公式判斷出這個(gè)號碼是偶數(shù)時(shí),顯示"女",否則,這個(gè)號碼是奇數(shù)的話,則返回"男"。(2)根據(jù)身份證號碼求出生日期=CONCATE
7、NATE("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))目的就是將多個(gè)字符串合并在一起顯示。(3)根據(jù)參加工
8、作時(shí)間求年資(即工齡)=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"個(gè)月")公式解釋:a.TODAY()用于求出系統(tǒng)當(dāng)前的時(shí)間b.DATEDIF(F4,TODAY(),"y")用于計(jì)算當(dāng)前系統(tǒng)時(shí)間與參加工作時(shí)間相差的年份c.DATEDIF(F4,TODAY(),"ym")用于計(jì)算當(dāng)前系統(tǒng)時(shí)間與參加工作時(shí)間相差的月份,忽略日期中的日和年。d.=CONCATENATE(D