For both employees and HR practioners, how to calculate annual leave balance correctly is critical. There are many basis for such calculation such as Anniversary Basis, Individual Leave Year, Calendar Year Basis or Common Leave Year. What do they mean exactly?

Also, how to use MICROSOFT EXCEL to maintain Annual Leavel calculation conveniently?

1. What is "Annivesary Basis" or "Individual Leave Year"?

"Annivesary Basis" or "Individual Leave Year" means that the number of leave entitled by the employee is determined by join date of the employee, and increase according to anniversary of join date. If referring to Employment Ordinance in Hong Kong, employee entitles 7 days of leave after the first anniversary since joining the company, 7 days of leave after the second anniversary and 8 days of leave after the third anniversary, and so on.

Concise Guide of Employement Ordinance - Rest Day, Statutory Leave and Annual Leave
https://www.labour.gov.hk/eng/public/wcp/ConciseGuide/04.pdf

Nevertheless, typically Hong Kong companies will offer more annual leaves than statutory required. On the other hand, employees do not have to wait for one year in order to entitle his entitlement of "first year". This practice is called "Entitled in Advance".

However, to demonstrate the regulatory requirement in the following example, we will not adopt the practice of "Entitled in Advance".

For example, assuming that

  • The employee joined the company on 2016/5/27.
  • After his first employment year, the employee entitles 7 days, 7 days, 8 days and 9 days respectively in his first to fourth year.

The number of annual leave entitled afterwards is as follow:


2. What is "Calendar Year Basis" or "Common Leave Year"?

Other than point 1 of the above, the company can also adopt the practice of "Common Leave Year", which means regardless of join date of different employees, their entitlements of annual leave are determined on the same date (such as 1st of Jan) of each year.

With the same assumptions:

  • The employee joined the company on 2016/5/27.
  • After his first employment year, the employee entitles 7 days, 7 days, 8 days and 9 days respectively in his first to fourth year.

The number of annual leave entitled afterwards is as follow. Pay attention that in the first year, the number of entitlement has to be pro-rated according to join date. Also, according to Employment Ordinance, whether it is leap year or not, the denominator in the pro-rata calculation must be 365 instead of 366. Decimal places are rounded up to integer.

In the above example, the entitlement in 2017 is:

  • Number of day from 2016/5/27 to 2016/12/31: 219
  • 219 / 365 = 0.6

Entitlement = 7 x 0.6 = 4.2
Rounding Up: 4.2 -> 5 Days

Common Leave Year under Employment Ordinance:
http://www.blis.gov.hk/blis_ind.nsf/DA97F6A8ED400207482564820006B580/98D81D427C12DBDE48257EFA000E2AC8?OpenDocument

"Common Leave Year" As Explained in Concise Guide of Employment Ordinance:
https://www.labour.gov.hk/eng/public/wcp/ConciseGuide/04.pdf


3. Can We Combine The Two Approaches in the Above?

Yes. We can refer to "Annivesary Basis / Individual Leave Year" for number of entitlement of particular year, but the actual entitlement is only determined or revised on the start date of "Calendar Year Basis / Common Leave Year". We call it "Hybrid Basis".

Following the same assumption on join date and scale:

In the above example, the entitlement of 5 days in 2017 is calculated using the same logic in point 2 of the above.

In 2019, the entitlement in 2019 is:

  • 7 x (146 / 365) + 8 x (219 / 365) = 7.6 Days
  • Within which 146 days is from 2018/1/1 to 2018/5/27; 219 days is from 2018/5/27 to 2018/12/31

4. Can You Give Me Excel Template of The Above for Reference?

As you partner we have already prepared the Excel file for you to download, which demonstrates the calculation if, and if not, adopting the practice of "Entitled in Advance".

Download:excel_example.xls

You may also refer to elaboration by Microsoft on Excel functions such as "hlookup" and "vlookup":

HLOOKUP

VLOOKUP


To make your HR work more easily, you can also refer to the following blogs regarding the use of Excel.

Share with your friend if the above is useful to you!