編按：

HR必會(huì)幾個(gè)日期函數(shù)，才能輕松核查員工轉(zhuǎn)正日期、核算不同員工的不同年假時(shí)長(zhǎng)（按小時(shí)算）……明天就是端午小假了，面對(duì)想要把年假和端午假期一起放的員工來說，今天是興奮的一天；可是對(duì)于HR來說，快速整理員工可放假的時(shí)長(zhǎng)并及時(shí)回復(fù)員工，就是一件很考驗(yàn)工作效率的事！下面，一起來看看如何快速核算假期時(shí)長(zhǎng)吧！另外還有核算轉(zhuǎn)正日期和合同結(jié)束日期，2個(gè)HR必會(huì)日期函數(shù)！

總是有人問：

“最常用的函數(shù)是什么？”

“什么函數(shù)是必須學(xué)會(huì)的？”

……

類似的問題有好多。

其實(shí)常用的函數(shù)和必會(huì)的函數(shù)是一個(gè)很籠統(tǒng)的說法，因人而異。不同的崗位不同的工作，遇到的問題是五花八門的，解決問題所用到的函數(shù)也是不同的。

以HR為例，不同的公司必定會(huì)用到的函數(shù)都沒有唯一標(biāo)準(zhǔn)，但是如果將應(yīng)用場(chǎng)景明確的話，什么是必會(huì)的函數(shù)，似乎還是有跡可循的。

小李是某公司人資部，專門負(fù)責(zé)員工合同管理和員工年假核算的員工。對(duì)他來說，經(jīng)常遇到的問題，如下圖的表格：

表格中已有的內(nèi)容都是基礎(chǔ)資料，空白的三列就是他的主要工作。

那么對(duì)小李來說，什么函數(shù)是必須要會(huì)的，相信大家看完今天的教程都能知道答案。

接下來咱們一起幫小李把這三列公式補(bǔ)全。

一、轉(zhuǎn)正日期

已知入職日期和試用期，如何計(jì)算出轉(zhuǎn)正日期？

這個(gè)問題不難理解，例如入職日期為2018年4月2日，試用期3個(gè)月，轉(zhuǎn)正日期就是2018年7月2日。

正確結(jié)果應(yīng)該是這樣的：

不難是不難，但要自己一個(gè)一個(gè)的算好再去填嗎？

肯定不需要！那么要用什么公式和函數(shù)才能自動(dòng)計(jì)算出轉(zhuǎn)正日期呢？

答：EDATE函數(shù)。

這個(gè)函數(shù)的功能就是得到某個(gè)日期在指定月數(shù)之后的一個(gè)日期。

格式為：=EDATE（起始日期，指定月數(shù)）

放在本例中，起始日期就是入職日期，指定月數(shù)就是試用期。

因此最終的公式為：=EDATE(C2,D2)

二、合同結(jié)束日期

已知合同開始日期和合同期限，如何計(jì)算合同結(jié)束日期？

實(shí)際上這個(gè)問題和第一個(gè)問題如出一轍，區(qū)別在于實(shí)習(xí)期的單位是月，而合同期限的單位是年。

如果繼續(xù)使用EDATE函數(shù)的話，指定月數(shù)能不能直接使用指定年數(shù)呢？

答案是不行！但是可以變通一下！

因?yàn)橐荒暧惺€(gè)月這是固定的，所以可以把公式改成：EDATE（合同開始日期，合同期限*12）

套入表格中，公式就是：=EDATE(F2,H2*12)

三、最后一個(gè)問題——年假核算。

這個(gè)問題和前兩個(gè)完全不一樣。因?yàn)椴煌?，年假的?guī)則也是不同的。

小李他們公司的年假是這樣規(guī)定的：入職滿一年后，每年享有5天年假，每天按8小時(shí)核算，每次休假按4小時(shí)一次使用，因此年假需要核算到小時(shí)（h）。另外，入職滿一年后年假才開始計(jì)入，比如說入職一年到一年半之間的時(shí)候，只擁有2.5天年假（年假中的另外2.5天需入職一年半到兩年之間才擁有），合計(jì)增加年假20小時(shí)。

感覺這是一道數(shù)學(xué)題了。怎么設(shè)計(jì)一個(gè)公式來自動(dòng)核算年假呢？

這里面有幾個(gè)關(guān)鍵步驟：

1.要統(tǒng)計(jì)出入職一年的時(shí)間；

2.要計(jì)算出現(xiàn)在距離入職一年的這個(gè)時(shí)間有多久（幾個(gè)月），這個(gè)時(shí)間也就是實(shí)際核算年假的基礎(chǔ)；

3.根據(jù)統(tǒng)計(jì)出的核算年假的月數(shù)，按照每年5天的標(biāo)準(zhǔn)分?jǐn)偟矫總€(gè)月，同時(shí)按照4小時(shí)為單位進(jìn)行核算，最后按照統(tǒng)一的格式來顯示核算結(jié)果。

最后的效果是這樣的：

公式為：=IFERROR(INT(FLOOR(DATEDIF(EDATE(C2,12),TODAY(),"M")/12*5*8,4)),0)&"小時(shí)"

如何理解這個(gè)公式，又如何才能自己完成這個(gè)公式呢？

篇幅所限，就不一一詳細(xì)解釋這個(gè)公式了。

下面簡(jiǎn)單的幫大家拆解一下其中的原理：

①在這個(gè)公式中，最里面的一層是EDATE(C2,12)，和前面兩個(gè)問題一樣，最終得到的是入職一年的日期。

②公式的第二層是DATEDIF(EDATE(C2,12),TODAY(),"M")，DATEDIF也是公眾號(hào)里多次講過的函數(shù)，此處的作用是計(jì)算現(xiàn)在距離轉(zhuǎn)正一年還有幾個(gè)月。

③第三層是一堆數(shù)字計(jì)算，INT(FLOOR(DATEDIF()/12*5*8,4))，就是將DATEDIF計(jì)算出的月數(shù)除以12（一年12個(gè)月），乘以5（每年5天年假）再乘以8（每天年假按8小時(shí)計(jì)算）換算成年假的小時(shí)數(shù)，再以4為基數(shù)取整得到一個(gè)可以實(shí)際使用的年假小時(shí)數(shù)。

④最后一層是美化了，使用IFERROR將錯(cuò)誤值顯示為0，并且加了&"小時(shí)"，顯示出核算結(jié)果的單位。

PS：

想要詳細(xì)了解這個(gè)嵌套公式，大家可以查看下面的教程！

函數(shù)組合思維,你有嗎?

靈活組合或嵌套函數(shù)編寫公式解決問題的技巧2：參數(shù)替換法

在這個(gè)公式中，初學(xué)者比較難理解的可能是第三層的這些數(shù)學(xué)函數(shù)，實(shí)際上這類函數(shù)還挺多，有興趣的朋友掃描下方二維碼，到公眾號(hào)下留言，咱們單獨(dú)來一期數(shù)學(xué)計(jì)算類的函數(shù)介紹。

最后一個(gè)問題，小李必須學(xué)會(huì)的函數(shù)到底是哪個(gè)，你看出來了嗎？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

HR的福音！一張能自動(dòng)提醒面試的excel表格，面試信息再多也不怕看漏！

3個(gè)日期函數(shù)，讓你輕松搞定90%的日期計(jì)算問題

9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏）

玩轉(zhuǎn)excel日期函數(shù)，一文看懂90%的日期運(yùn)算

版權(quán)申明：

本文作者郅龍；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。