最新手把手教您用excel为员工核定年休假天数

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

手把手教您用excel为员工核定年休假天数

带薪年休假（PaidAnnualLeave）简称年休假，是指职工连续工作一年以上

可以享受的一定时间的带薪的假期，是我国员工福利制度的一项重要组成部分。

很多HR新手往往不知道该从何着手去理解年休假的政策法规，以及应该用何

种方式为员工准确核定年休假，这样较易导致员工无法及时享受到应有的福利、

影响劳动关系的和谐稳定。

本人以前核算过企业5000多名员工2012年度年休假的工作，由于单位人数

众多、人员类别又多样化，核算过程的艰辛可想而知，当然从中也积累了一些

实用的心得、体会与大家一同分享～。

政策法规篇

要想准确核定年休假，首先要对当前年休假的相关政策法规有一个全面的了

解，可供研读的文件有三个，分别是：《职工带薪年休假条例》（国务院令第

514号）、《企业职工带薪年休假实施办法》（人社部令第1号）以及《关于<

企业带薪年休假>有关问题的复函》（人社厅函（2009）149号）。

文件中几乎每个条款都可以进行深入的研究，本文主要选取其中和年休假天

数核定有关的几个条款和常见案例加以解析：

（一）、工龄和年休假的关系

案例1：“刚大学毕业的小张和有30年社会工龄的老王是同一时间进某单位

工作的，工作岗位、性质也都一样，一年以后小张得知自己可以开始休年休假

1

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

了，很高兴，但随后他发现老王从去年就开始享受年休假了，而且今年年休假

的天数还比自己多10天，小张心想，难道是单位核定错了吗？”

《职工带薪年休假条例》中的第二条和第三条分别规定“职工连续工作1年

以上的，享受带薪年休假”、“职工累计工作已满1年不满10年的，年休假5

天；已满10年不满20年的，年休假10天；已满20年的，年休假15天。”第

二条说的是“什么时候开始享受年休假”、第三条说的是“到底能够享受几天”。

搞清楚这两条的关系就可以解释本案例了，小张和老王虽然是同一时间进单位

的，但由于其累计社会工龄不同造成最终享受的年休假起始时间和天数的不同。

老王在进单位前已经曾经连续工作一年以上，所以新进单位其实就符合享受年

休假的条件，只不过当年的享受天数要再进行折算（详见下文），而小张大学

刚毕业没有社会工龄，必须连续工作一年以上才能开始享受年休假。另外，两

人的累计社会工龄也不同，一年以后，小张累计社会工龄是1年，老王累计社

会工龄是31年，所以小张的年休假天数和老王的年休假天数肯定是不同的，这

也体现了工龄越长福利越好的基本原则。

（二）、休假起始日和天数折算

案例2：“刚大学毕业的小张2011年7月1日进单位工作，他知道要做满一

年才有年休假，但是这一年是怎么算的呢？是明年7月1日还是明年1月22日

（过年）开始就有？做满一年后，也是休息5天吗？这5天应该在啥时候休完？”

本案中小张是应届毕业生，以前没有工龄，所以在新单位应至少工作满1年，

即从2012年7月1日起开始享受2012年度的年休假。年休假一般情况下都是

休当年度的，而且这个“当年度”是指自然年度，即当年的1月1日到12月31

日。小张是2012年7月1日才符合享受条件的，所以根据《企业职工带薪年休

假实施办法》第五条的规定需要进行折算后才能计算出他2012年7月1日到2012

年12月31日之间还能使用的年休假天数，具体折算方式如下：

2

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

（当年度在本单位剩余日历天数÷365天）×职工本人全年应当享受

的年休假天数

“当年度在本单位剩余日历天数”可以从日历上数出来，也就是7月

1日到12月31日一共有几天；“职工本人全年应当享受的年休假天数”

根据上面说到的《条例》第三条的规定可以得知，小张是5天，所以折

算公式为：

[（31+31+30+31+30+31）÷365]×5=2.5205……

根据政策规定，折算后小数点之后的数字将全部舍去，也就是说小张

最后可以享受的2012年度的年休假天数为2天，截止日期到2012年

12月31日为止。从2013年1月1日到2013年12月31日之间，正好

是一个自然年度，就不用再进行折算了，即2013年度小张可以休5天。

（三）、社会工龄的核定方法

根据以上2点可以看出，年休假天数核定的基础就是员工的累计社会工龄，

那么累计社会工龄核定的基础又是什么呢？

《关于<企业带薪年休假>有关问题的复函》中的第二点对此做了详细解释，

本文中就不再赘述了。在此笔者重点提醒一下：员工依法服兵役期间也应计算

为累计社会工龄，即军龄算作工龄，HR可以根据员工提供的《退伍证》等有效

证明将此年限计算进去。

实际操作篇

1、前期核定工作——先根据该员工的劳动手册或人事档案中其他相关证明材

料上的社会工龄记录进行工龄的核算，确定一共有多少个月的社会工龄，以及

是否曾经连续工作一年以上。并将相关原始资料进行备份、归档。日后如果有

3

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

新进员工，也可以在办理入职手续时让其签署工龄确认表，让员工对HR的核定

结果进行签字确认，以避免日后产生不必要的纠纷。

2、计算准备工作——新建一张excel表格，将本单位入职时间录入A2单元

格（格式最好设为“年-月-日”，如2011-02-03），社会工龄工作年限可用公

式在B2单元格计算【公式：总社会工龄的月份数/12】（如果要做得细一点，

也可以先把总社会工龄的月份数录B2单元格，即每进来一个员工都核定一下，

然后录入，再在C列用公式：=B2/12拖动句柄操作）

接下来就是核算员工年休假的重头戏了

老员工算法

3、针对去年就在单位工作的老员工——先要计算其在“本单位工作年限”，

可用公式在C2单位格计算，以计算2012年年休假为例【公式：

=("2011-12-31"-A2+1)/365】

公式解析：因为老员工的工龄除了在外单位工作的工龄外还要加上在本单位

工作的工龄，所以先算截止当年年底该员工一共做了几天，即用当年年底的“年

月日”减去该员工入职本单位的“年月日”再加1天（如果不加上这1天最后

算出来可是会少一天的哦）算出天数除以365天即可算出做了几年。

再次提醒：工龄是核定年休假天数的基础，一定要做实做细、按实操作。

4、有了社会工龄（第2点中得出）和本单位工龄（第3点中得出）后就应该

进行工作年限的汇总，可在D2单元格设置工龄汇总【公式：=B2+C2】，接着选

择D2单元格用exce中的条件格式功能将大于10年工龄和大于20年工龄的人

用不同颜的块标识出来，具体操作（因excel版本不同会有些许差异）：

开始》》》条件格式》》》新建规则》》》选择规则类型“只为包含以下内容

4

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

的单元

值，大于或等于20用红，大于或等于10用绿。

格设置格式”，单元格

当然，此处用条件格式来做虽然简单但对某些有更高追求的同志来说还是希

望用公式来实现此功能，所以下文（新员工算法）中还会介绍另一种用公式直

接计算的方法，两种方法都可以达到需要的效果。

5、对不同社会工龄的老员工用块标出后，再用excel中的筛选功能筛选不

同块，比如筛选绿块，则所有总工作年限大于等于10年但小于20年的

员工都可以筛选出来了，E2列可以作为当年可休年休假天数的核定列，直接手

动输入10，拖动句柄复制即可。同样方法可以将工龄大于20年的员工筛选后在

E2列录入15，将无块的筛选后录入5，即可完成全员核算工作。这样，只要

前期核定工龄正确，即使是上百人、上千人也可以在很短的时间内进行年休假

天数的快速核定。

新员工算法

新进单位员工算法较为复杂，需要分为两类：

5

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

6、第一类是新进单位但在之前有工作经验且已经曾经连续工作一年以上的员

工——这类员工按照目前人保局关于年休假的口径规定刚进新单位就应当给予

其法定年休假（上文案例1中也有提及），由于一般此类员工不会正巧是1月1

日进单位的，所以需要进行折算才能确定当年度应该给他休的年休假天数（具

体法条可参见：《企业职工带薪年休假实施办法》以及上文中的案例2）。

假设王某符合上述条件，是2011年2月3日进本单位的工作，问他2011年

能享受几天年休假？

公式如下：

【=TRUC(("2011-12-31"-A2+1)/365*IF(AD(D2<10,D2>0),5,IF(and(D2>=1

0,D2<20),10,IF(D2>=20,15,0))))】

公式解析：这个公式看似比较复杂，我们需要以中间的乘号为界限，分前半

段和后半段进行分析。

前半段【"2011-12-31"-A2+1)/365】这个公式其实就是计算从员工新进单位

时间到本单位剩余的日历天数后再除以365天进行天数计算（即1号令第五条

中规定的“当年度在本单位剩余日历天数÷365天”）。

后半段

【IF(AD(D2<10,D2>1),5,IF(and(D2>=10,D2<20),10,IF(D2>=20,15,0))】是职

工累计工作年限所对应的年休假法定天数的计算，即“职工累计工作已满1年

不满10年的，年休假5天；已满10年不满20年的，年休假10天；已满20年

的，年休假15天。”（可以看出，此段公式就可替代上文老员工算法中第4点

的条件格式，所以利用excel可尝试多种做法，最后结果其实都一样的。

将以上两个公式算下来的结果相乘，会得出一个小数，比如

4.564……，根据年休假实施办法的规定“折算后不足1整天的

6

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

部分不享受年休”，所以用TRUC函数对计算结果进行取整（小数点后都舍

去），最后此人2011年度可享受年休假天数可以计算出来，为4天。

只要这个对这条公式充分理解了，那么其它人员的年休假核算都不是难事了。

7、第二类新进人员是指以前从来没有参加过工作，现在新进单位工作的职工，

比如应届毕业生，这类职工没有社会工龄，应该从他毕业后与单位建立劳动关

系后计算其工龄，在本单位连续工作一年以后再享受国家法定年休假。

所以这类员工应该比其他员工多一列——“年休假享受起始日期”（假设在g

列），以确定他第二年从哪一天开始符合享受年休假的条件，即他的入职日期

往后推算一年的日期，所以g2单位格【公式：

=DATE(YEAR(A2)+1,MOTH(A2),DAY(A2))】，比如应届生小王的入职日期是

2011-02-03，用此公式计算后，年休假享受起始日期为“2012-02-03”。

当然，2012-2-3以后他能享受几天年休假也是要进行折算的，使用的公式和

上文第6点是相似的，把前半段公式【"2011-12-31"-A2+1)/365】改为

【"2012-12-31"-g2+1)/365】即可。

企业福利假算法

除了国家法定年休假以外，很多企业为了完善本企业的福利体制、增强人才

竞争力，会制定有福利年休假，也就是额外多给员工的年休假，当然这种算法

是按照各个企业的规则制度来的。现在多数企业的做法是先做满一年，然后按

照员工在本企业的工作年限给予福利年休假，但是一般福利年休假会随着法定

年休假的增加而减少（因为老员工工龄长，法定年休假假期也长，所以福利年

休假可适当减少），以达到新老员工间的平衡。以我们企业为例：

7

148

149

150

151

152

153

154

按照上面的规定可以编制出如下公式

【=IF(A2<1,0,IF(AD(A2>=10,A2<=11),1,IF(OR(AD(A2>=1,A2<=3),AD(A2

>11,A2<=13)),2,IF(OR(AD(A2>3,A2<=5),AD(A2>13,A2<=15)),3,IF(OR(AD(

A2>5,A2<=7),AD(A2>15,A2<=17)),4,IF(OR(AD(A2>7,A2<10),AD(A2>17,A2<

20)),5,0))))))】

8