院長大大已經開啟Excel函數系列課程,上班族、想學Office相關技術等等有需要的朋友請關注院長,享受每天及時的知識分享,跟著院長走,輕輕鬆鬆玩轉Office。
日期時間函數講完了,朋友們都掌握了麼?
今天我們總結之前學過的日期系列函數,看看他們有多強大。
1、計算員工年齡
·YEAR+NOW/TODAY
=YEAR(NOW())-YEAR(E4)
NOW返回當前的時間,第一個YEAR求現在的年份,第二個YEAR求出生年份,兩個相減就是實際的年齡。(糾結虛歲的請拉到最下面,掃碼關注院長。)
再試試TODAY函數。
=YEAR(TODAY())-YEAR(E4)
同理,求出現在和出生的年份相減即是實際年齡。
·DATEDIF+NOW/TODAY+IF
=DATEDIF(E4,TODAY(),"y")
院長看到過有些人事的朋友會用這個函數。
這個函數有一個致命缺陷,當出生的月份小於當前的月份是準確的,但是超出的時候就會小一歲。
想想當你打開這張表的時候某人突然加了一歲,還以為自己函數寫錯了,然後費時間修改,最後認為這個函數有問題,那就得不償失了。
這裡鄙視那些沒有實踐就發文章的人。
別問院長是怎麼發現的!
這裡我們可以這樣改:
=IF(MONTH(E4) 可以看到結果是不是變準確了呢? 上述函數先判斷月份的大小,小於當前月的正常計算,否則加一年就行。 此函數用來裝逼還是可以的,追求極簡效率的建議用YEAR函數。 2、根據工齡計算年假天數 此時用DATEDIF是合適的,不足一年的公司肯定不會給你算一年的,這時候要是用YEAR的話鍋就不能甩給函數了。 =DATEDIF(D4,TODAY(),"y") 函數中出錯的是因為入職時間超出了當前時間,不是院長水啊。數據是院長隨機生成的。 根據右表的年假數我們計算一下每個人的年假數。 最簡單的辦法就是VLOOKUP函數一波帶走。 當然一般情況下是沒有右表做參考的。 這裡有人會推薦IF,IF院長還沒講呢,當然不會在這裡用它。(糾結IF的人,來不及多說了,趕緊最下方關注院長。) 院長開公眾號的時候給自己設置了個遊戲規則,儘量不用沒講過的函數做演示。對就是遊戲規則,跟著院長學下去,總有一天你也可以把這當做遊戲一樣簡單有趣! 所以院長會這麼簡單就結束這麼有意思計算麼? 那麼,請注意! 前方高能! 非戰鬥人員火速撤離! 還記得之前講的查找函數麼? ·LOOKUP 這裡院長先排序,方便大家確認函數的正確性。 =LOOKUP(E4,{0,1,2,3,4,5,6;0,3,3,4,4,5,5}) 這裡把右表看做每列是一組,共兩組。 注意:系列之內用英文狀態下的逗號,系列之間用英文狀態下的分號。 LOOKUP的數組形式還記得麼,忘記的請寫上100遍。 ·VLOOKUP 這裡把右表看做每行是一組,共7組。每組是兩個數據(A,B),第三個參數為2就是查找B的值。 ·HLOOKUP =HLOOKUP(E4,{0,1,2,3,4,5,6;0,3,3,4,4,5,5},2,0) 這裡把右表看做每列是一組,共兩組。 有些時候不便窮盡所有的年數,那這些函數還能用麼? 比如我們把2年放3天的數據刪掉。 還記得第四個參數0和1的區別的,這裡是精確匹配,所以出錯,改為模糊匹配就行了。(忘記的100遍) =VLOOKUP(E4,{0,0;1,3;3,4;4,4;5,5;6,5},2,1) 醬紫,就可以了。 怎麼樣,查找系三大戰神VLOOKUP、HLOOKUP、LOOKUP這麼用是不是很清爽! 現在,還敢說你瞭解這三個函數麼? 3、考勤打卡的計算 ·VLOOKUP+HOUR =VLOOKUP(HOUR(D4),{0,"未打卡";1,"√";9,"遲到"},2,1) 先用HOUR算出時間,然後用數組{}模糊匹配出三種情況。 同理,求出下午打卡情況。 ·HLOOKUP+HOUR =HLOOKUP(HOUR(E4),{0,1,18;"未打卡","早退","√"},2,1) 以上函數可以用LOOKUP、VLOOKUP、HLOOKUP互換。 延伸:計算一下員工一天的工作時長。 注意:表中的上下班時間為文本格式,不是時間格式。這是TIMEVALUE函數強制的格式,時間格式會出錯。
4、員工轉正
=DATE(YEAR(E4),MONTH(E4)+3,DAY(E4)-1)
第二個參數+3表示3個月後。
第三個參數-1為自然月的整月,不減一則為第4個月的第一天。
5、合同到期
=DATE(YEAR(E4)+1,MONTH(E4),DAY(E4)-1)
院長每天堅持發,你還不每天堅持學???
寫在正文之後:院長大大開始也是小白,知道不會用Office去求人的痛苦,所以大大開了這個公眾號,希望大家都能擺脫困境,大家可以轉發給同事朋友一起學習進步,而不是拿去賺取蠅頭小利。
希望大家多多練習以上函數使用方法,早日告別Excel小白。後期院長還會增設PPT,Word相關知識分享,還等什麼,火速關注院長,暢享最新鮮的Office知識。
閱讀更多 Officefuli院 的文章