最新手把手教您用excel為員工核定年休假天數(shù)

1、1234567891011121314151617181920手把手教您用excel為員工核走年休假天數(shù)帶薪年休假（Paid Annual Leave）簡稱年休假，是指職工連續(xù)工作一年以上 可以享受的一定時(shí)間的帶薪的假期，是我國員工福利制度的一項(xiàng)重要組成部分。很多HR新手往往不知道該從何著手去理解年休假的政策法規(guī)，以及應(yīng)該用何 種方式為員工準(zhǔn)確核定年休假，這樣較易導(dǎo)致員工無法及時(shí)享受到應(yīng)有的福利、 影響勞動關(guān)系的和諧穩(wěn)定。本人以前核算過企業(yè)5000多名員工2012年度年休假的工作，由于單位人數(shù) 眾多、人員類別又多樣化，核算過程的艱辛可想而知，當(dāng)然從中也積累了一些 實(shí)用的心得、體會與大家一同分享

2、。政策法規(guī)篇要想準(zhǔn)確核定年休假，首先要對當(dāng)前年休假的相關(guān)政策法規(guī)有一個(gè)全面的了 解，可供研讀的文件有三個(gè)，分別是：職工帶薪年休假條例（國務(wù)院令第 514號）、企業(yè)職工帶薪年休假實(shí)施辦法（人社部令第1號）以及關(guān)于 企業(yè)帶薪年休假有關(guān)問題的復(fù)函（人社廳函（2009） 149號）。文件中幾乎每個(gè)條款都可以進(jìn)行深入的研究，本文主要選取其中和年休假天 數(shù)核定有關(guān)的幾個(gè)條款和常見案例加以解析：（一）、工齡和年休假的關(guān)系案例1： “剛大學(xué)畢業(yè)的小張和有30年社會工齡的老王是同一時(shí)間進(jìn)某單位 工作的，工作崗位、性質(zhì)也都一樣，一年以后小張得知自己可以開始休年休假了，很高興，但隨后他發(fā)現(xiàn)老王從去年就開始享受年休假

3、了，而且今年年休假 的天數(shù)還比自己多10天，小張心想，難道是單位核定錯(cuò)了嗎？ ”212223242526272829303132333435363738394041424344職工帶薪年休假條例中的第二條和第三條分別規(guī)定“職工連續(xù)工作1年 以上的，享受帶薪年休假”、“職工累計(jì)工作已滿1年不滿10年的，年休假5 天；已滿10年不滿20年的，年休假10天；已滿20年的，年休假15天?！钡?二條說的是“什么時(shí)候開始享受年休假”、第三條說的是“到底能夠享受幾天”。 搞清楚這兩條的關(guān)系就可以解釋本案例了，小張和老王雖然是同一時(shí)間進(jìn)單位 的，但由于其累計(jì)社會工齡不同造成最終享受的年休假起始時(shí)間和天數(shù)的不同

4、。 老王在進(jìn)單位前已經(jīng)曾經(jīng)連續(xù)工作一年以上，所以新進(jìn)單位其實(shí)就符合享受年 休假的條件，只不過當(dāng)年的享受天數(shù)要再進(jìn)行折算（詳見下文），而小張大學(xué) 剛畢業(yè)沒有社會工齡，必須連續(xù)工作一年以上才能開始享受年休假。另外，兩 人的累計(jì)社會工齡也不同，一年以后，小張累計(jì)社會工齡是1年，老王累計(jì)社 會工齡是31年，所以小張的年休假天數(shù)和老王的年休假天數(shù)肯定是不同的，這 也體現(xiàn)了工齡越長福利越好的基本原則。（二）、休假起始日和天數(shù)折算案例2： “剛大學(xué)畢業(yè)的小張2011年7月1日進(jìn)單位工作，他知道要做滿一 年才有年休假，但是這一年是怎么算的呢？是明年7月1日還是明年1月22日 （過年）開始就有？做滿一年后，也是

5、休息5天嗎？這5天應(yīng)該在啥時(shí)候休完？ ”本案中小張是應(yīng)屆畢業(yè)生，以前沒有工齡，所以在新單位應(yīng)至少工作滿1年, 即從2012年7月1日起開始享受2012年度的年休假。年休假一般情況下都是 休當(dāng)年度的，而且這個(gè)“當(dāng)年度”是指自然年度，即當(dāng)年的1月1日到12月31 日。小張是2012年7月1日才符合享受條件的，所以根據(jù)企業(yè)職工帶薪年休 假實(shí)施辦法第五條的規(guī)定需要進(jìn)行折算后才能計(jì)算出他2012年7月1日到2012 年12月31日之間還能使用的年休假天數(shù)，具體折算方式如下：245464748495051525354555657585960616263646566（當(dāng)年度在本單位剩余日歷天數(shù)*365天）X

6、職工本人全年應(yīng)當(dāng)享受 的年休假天數(shù)“當(dāng)年度在本單位剩余日歷天數(shù)”可以從日歷上數(shù)出來，也就是7月 1日到12月31日一共有幾天；“職工本人全年應(yīng)當(dāng)享受的年休假天數(shù)” 根據(jù)上面說到的條例第三條的規(guī)定可以得知，小張是5天，所以折 算公式為：（31+31+30+31+30+31）十365X5=2.5205根據(jù)政策規(guī)定，折算后小數(shù)點(diǎn)之后的數(shù)字將全部舍去，也就是說小張 最后可以享受的2012年度的年休假天數(shù)為2天，截止日期到2012年 12月31日為止。從2013年1月1日到2013年12月31日之間，正好 是一個(gè)自然年度，就不用再進(jìn)行折算了，即2013年度小張可以休5天。（三）、社會工齡的核定方法根據(jù)以

7、上2點(diǎn)可以看出，年休假天數(shù)核定的基礎(chǔ)就是員工的累計(jì)社會工齡， 那么累計(jì)社會工齡核定的基礎(chǔ)又是什么呢？關(guān)于企業(yè)帶薪年休假有關(guān)問題的復(fù)函中的第二點(diǎn)對此做了詳細(xì)解釋， 本文中就不再贅述了。在此筆者重點(diǎn)提醒一下：員工依法服兵役期間也應(yīng)計(jì)算 為累計(jì)社會工齡，即軍齡算作工齡，HR可以根據(jù)員工提供的退伍證等有效 證明將此年限計(jì)算進(jìn)去。實(shí)際操作篇1、前期核定工作一一先根據(jù)該員工的勞動手冊或人事檔案中其他相關(guān)證明材 料上的社會工齡記錄進(jìn)行工齡的核算，確定一共有多少個(gè)月的社會工齡，以及 是否曾經(jīng)連續(xù)工作一年以上。并將相關(guān)原始資料進(jìn)行備份、歸檔。日后如果有新進(jìn)員工，也可以在辦理入職手續(xù)時(shí)讓其簽署工齡確認(rèn)表，讓員工對

8、HR的核定 結(jié)果進(jìn)行簽字確認(rèn)，以避免日后產(chǎn)生不必要的糾紛。2、計(jì)算準(zhǔn)備工作一新建一張excel表格，將本單位入職時(shí)間錄入A2單元 格（格式最好設(shè)為“年-月-日”，如2011-02-03）,社會工齡工作年限可用公 式在B2單元格計(jì)算【公式：總社會工齡的月份數(shù)/12】（如果要做得細(xì)一點(diǎn)， 也可以先把總社會工齡的月份數(shù)錄B2單元格，即每進(jìn)來一個(gè)員工都核定一下， 然后錄入，再在C列用公式：=B2/12拖動句柄操作）接下來就是核算員工年休假的重頭戲了老員工算法3、針對去年就在單位工作的老員工一一先要計(jì)算其在“本單位工作年限”， 可用公式在C2單位格計(jì)算，以計(jì)算2012年年休假為例【公式：= r2011-

9、12-31*-A2+l）/365公式解析：因?yàn)槔蠁T工的工齡除了在外單位工作的工齡外還要加上在本單位 工作的工齡，所以先算截止當(dāng)年年底該員工-共做了幾天，即用當(dāng)年年底的“年 月日”減去該員工入職本單位的“年月日”再加1天（如果不加上這1天最后 算出來可是會少一天的哦）算出天數(shù)除以365天即可算出做了幾年。再次提醒：工齡是核定年休假天數(shù)的基礎(chǔ)，一定要做實(shí)做細(xì)、按實(shí)操作。676869707172737475767778798081828384858687884、有了社會工齡（笫2點(diǎn)中得出）和本單位工齡（第3點(diǎn)中得出）后就應(yīng)該 進(jìn)行工作年限的匯總，可在D2單元格設(shè)置工齡匯總【公式：=B2+C2,接著選

10、 擇D2單元格用exce中的條件格式功能將大于10年工齡和大于20年工齡的人 用不同顏色的色塊標(biāo)識出來，具體操作（因excel版本不同會有些許差異）： 開始條件格式新建規(guī)則選擇規(guī)則類型“只為包含以下內(nèi)容889909192939495969798991001011021031?|Xttggawga（£）：荃于SB 05沒IS所看單死將的銘武只為包含氏下內(nèi)靱皿無格設(shè)畫幣丈僅對祥名童前戒:怎的發(fā)侑設(shè)應(yīng)胳式僅對裔于取低于干均個(gè)的散血逐量裕衣便用公式聽走要設(shè)式的元矗M鎮(zhèn)規(guī)剣說明O的單元 格設(shè)置格式”,單元格值，大于或等于20用紅色，大于或等于10用綠色。當(dāng)然，此處用條件格式來做雖然簡單但對某些

11、有更高追求的同志來說還是希 望用公式來實(shí)現(xiàn)此功能，所以下文（新員工算法）中還會介紹另一種用公式直 接計(jì)算的方法，兩種方法都可以達(dá)到需要的效果。5、對不同社會工齡的老員工用色塊標(biāo)出后，再用excel中的篩選功能篩選不 同色塊，比如篩選綠色色塊，則所有總工作年限大于等于10年但小于20年的 員工都可以篩選出來了，E2列可以作為當(dāng)年可休年休假天數(shù)的核定列，直接手 動輸入10,拖動句柄復(fù)制即可。同樣方法可以將工齡大于20年的員工篩選后在 E2列錄入15,將無色塊的篩選后錄入5,即可完成全員核算工作。這樣，只要 前期核定工齡正確，即使是上百人、上千人也可以在很短的時(shí)間內(nèi)進(jìn)行年休假 天數(shù)的快速核定。新員工

12、算法新進(jìn)單位員工算法較為復(fù)雜，需要分為兩類：1041051061071081091101111121131141151161171181191201211221231241251266、第一類是新進(jìn)單位但在之前有工作經(jīng)驗(yàn)且已經(jīng)曾經(jīng)連續(xù)工作一年以上的員 工一一這類員工按照目前人保局關(guān)于年休假的口徑規(guī)定剛進(jìn)新單位就應(yīng)當(dāng)給予 其法定年休假(上文案例1中也有提及)，由于一般此類員工不會正巧是1月1 日進(jìn)單位的，所以需要進(jìn)行折算才能確定當(dāng)年度應(yīng)該給他休的年休假天數(shù)(具 體法條可參見：企業(yè)職工帶薪年休假實(shí)施辦法以及上文中的案例2)。假設(shè)王某符合上述條件，是2011年2月3日進(jìn)本單位的工作，問他2011年

13、能享受幾天年休假？公式如下：=TRUNC (*201 l-12-3r-A2+l) /365*IF (AND (D2< 10, D2>0), 5, IF(and(D2>=l 0, D2<20), 10, IF (D2>=20,15, 0)】公式解析：這個(gè)公式看似比較復(fù)雜，我們需要以中間的乘號為界限，分前半 段和后半段進(jìn)行分析。前半段【"2011-12-31"-A2+l)/365】這個(gè)公式其實(shí)就是計(jì)算從員工新進(jìn)單位 時(shí)間到本單位剩余的日歷天數(shù)后再除以365天進(jìn)行天數(shù)計(jì)算(即1號令第五條 中規(guī)定的“當(dāng)年度在本單位剩余日歷天數(shù)J365天”)。后半段IF

14、(AND(D2<10, D2>1),5, IF(and(D2>=10, D2<20), 10, IF(D2>=20, 15, 0)】是職 工累計(jì)工作年限所對應(yīng)的年休假法定天數(shù)的計(jì)算，即“職工累計(jì)工作已滿1年 不滿10年的，年休假5天；已滿10年不滿20年的，年休假10天；已滿20年 的，年休假15天?！?可以看出，此段公式就可替代上文老員工算法中第4點(diǎn) 的條件格式，所以利用excel可嘗試多種做法，最后結(jié)果其實(shí)都一樣的。將以上兩個(gè)公式算下來的結(jié)果相乘，會得出一個(gè)小數(shù)，比如4. 56164383561644，根據(jù)年休假實(shí)施辦法的規(guī)定“折算后不足1整天的部分不享受年休

15、”，所以用TRUNC函數(shù)對計(jì)算結(jié)果進(jìn)行取整(小數(shù)點(diǎn)后都舍 去)，最后此人2011年度可享受年休假天數(shù)可以計(jì)算出來，為4天。127128129130131132133134135136137138139140141142143144145146147只要這個(gè)對這條公式充分理解了，那么其它人員的年休假核算都不是難事了。7、第二類新進(jìn)人員是指以前從來沒有參加過工作，現(xiàn)在新進(jìn)單位工作的職工, 比如應(yīng)屆畢業(yè)生，這類職工沒有社會工齡，應(yīng)該從他畢業(yè)后與單位建立勞動關(guān) 系后計(jì)算其工齡，在本單位連續(xù)工作一年以后再享受國家法定年休假。所以這類員工應(yīng)該比其他員工多一列一一“年休假享受起始日期”(假設(shè)在g 列)，以確

16、定他第二年從哪一天開始符合享受年休假的條件，即他的入職日期 往后推算一年的日期，所以g2單位格【公式：=DATE (YEAR (A2) +1, MONTH (A2), DAY (A2)】，比如應(yīng)屆生小王的入職日期是 2011-02-03,用此公式計(jì)算后，年休假享受起始日期為“2012-02-03”。當(dāng)然，2012-2-3以后他能享受幾天年休假也是要進(jìn)行折算的，使用的公式和 上文第6點(diǎn)是相似的，把前半段公式【"2011-12-31"-A2+l)/365】改為*2012-12-31*-g2+l)/365即可。企業(yè)福利假算法除了國家法定年休假以外，很多企業(yè)為了完善本企業(yè)的福利體制

17、、增強(qiáng)人才 競爭力，會制定有福利年休假，也就是額外多給員工的年休假，當(dāng)然這種算法 是按照各個(gè)企業(yè)的規(guī)則制度來的。現(xiàn)在多數(shù)企業(yè)的做法是先做滿一年，然后按 照員工在本企業(yè)的工作年限給予福利年休假，但是一般福利年休假會隨著法定 年休假的增加而減少(因?yàn)槔蠁T工工齡長，法定年休假假期也長，所以福利年 休假可適當(dāng)減少)，以達(dá)到新老員工間的平衡。以我們企業(yè)為例：4年W累計(jì)工作吋間10年;本公司工齡（年）<3<5<7<10公司福利年休假 天敎（天）2345本公司工齡（年）<11<13<15<17<19公司福刑年休 假天數(shù)（天）1234510年W累計(jì)工作時(shí)間20年:累計(jì)工朮時(shí)間手20年，公司福刑年休假対0天。148149 按照上面的規(guī)定可以編制出如下公式150 =IF(A2<1, 0, IF(AND(A2>=10, A2<=11