HR’s work involves tremendous amount of data. Ideally we can implement an automatic HR system to make our work (and life) much easier.
Nevertheless, if we have to stick with Excel to handle payroll, MPF of Hong Kong or even appraisal, is there any formula or function from Excel can help?
1. How to Calculate Number of Day of Work Net of Leaves?
Question：Some of the payroll item depends on actual number of day of work. For example, the four days from 28 Jan to 31 Jan are holiday in 2017. From 1 Jan to 31 Jan, excluding Saturday and Sunday, how many working days are there?
Answer：We can use the function =NETWORKDAYS(D2,D3,A2:A5)
D2 refers to the start date of the month “1 Jan”, D3 refers to the end date of the month “31 Jan”. A2:A5 refers to holiday/leave day of the month, which means the four days from 28 Jan to 31 Jan.
Explanation on “NETWORKDAYS” by Microsoft:
https://support.office.com/en-us/article/NETWORKDAYS-function-48e717bf-a7a3-495f-969e-5005e3eb18e7
2. How to Summarize “Relevant Income” of MPF, of “Taxable Income”?
Question：See the following example. There are various “PayType” for different employees, and each “PayType” may fall under different categories under MPF or taxation.
Which Excel’s function should I use to calculate the $120 “Relevant Income” under MPF for “Employee 1”?
Answer：We can use =MMULT(B2:D2,IF($B$7:$B$9="Y",1,0)).
B2:D2 refers to the payroll amount of 30, 60 and 90 of “Employee1”.
$B$7:$B$9 refers to indicator of whether the 3 “PayType” are “relevant income” or not under MPF.
Noted that because the formula of calculation involves not only particular numbers, but array of numbers. Therefore, after inputting the formula, we have to click “CTRL SHIFT ENTER” instead of just “ENTER”.
Explanation on “MMULT” by Microsoft:
https://support.office.com/en-us/article/MMULT-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb
3. How To Calculate Number of Employees on Leave Each Day?
Question：Sometimes we need to avoid too many employees taking leave on the same day. The following are the leave applications of 5 employees. How to calculate number of employees on leave on 6 Feb?
Answer：We can use =COUNTIF(A2:E11,"="&DATE(2017,2,6))
A2:E11 refers to date of leave applications of the 5 employees.
Explanation on “COUNTIF” by Microsoft:
https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
4. Want to Use Excel to Create Template for Appraisal?
Question：We need to define the scope of answer when creating template for appraisal’s questions. How to define the scope of possible answers in Excel?
Answer：Using the “Data Validation/資料驗證”, fill in the possible answers under “List/清單”:
Explanation on “Data Validation” by Microsoft:
EXCEL Template of the Above for Free Download!
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!
