Shulou(Shulou.com)11/24 Report--

For a HR, Excel is often used to process data at work, and if you don't know some functions, it will greatly reduce your work efficiency. Here are some function formulas commonly used in HR sorted out by Mr. Wang. I hope they will be helpful to you.

1. Count the number of working days how to get the number of working days between two dates. The formula is as follows:

= NETWORKDAYS (A2 ~ B2)

2. there are all the personnel and their departments in the statistics department headcount table. how can we quickly count the number of people in each department?

= COUNTIF (BJV BMIT D2)

3. Statistics: employees fail below 70 points, pass above 70 points, and excel at more than 80 points.

= IFB270, "fail", IFB280, "pass", "excellent")

4. Calculate the statistics of late and early departure attendance is one of the necessary jobs in HR. Here, we can count the situation of late and early departure by clocking in time for work and leaving work.

Late:

= IF (C2TIME (8pm 30jin0), "late",) leave early:

= IFD2TIME (171.30), "early departure",)

5. Calculate the length of service from entry to the present.

= DATEDIF (B2Today (), "y") & "year" & DATEDIF (B2Today (), "ym") & "month" & DATEDIF (B2day (), "md") & "Heaven"

6. Birthday reminder within 15 days by comparing the date of birth with the date of the current system, people who celebrate birthdays within 15 days will be reminded.

= TEXT (15-DATEDIF (C2-15Ma Today (), "yd"), "there are still 0 birthdays left today")

7, through the ID card number to extract the date of birth before we were taught to use other methods to achieve, today, let's take a look at the use of function formula extraction.

= TEXT (MID (B2heliol 7, 8), "0-00-00")

8, the date of birth to obtain the age first through the ID card number to extract the date of birth, and then through the extracted year, month and day to obtain the age.

= DATEDIF (C2Today (), "y")

9. Judge the gender of men and women by the ID card number.

= IF (MOD (MID (B2jin17jue 1), 2), "male", "female")

10. the retirement date is calculated at the age of 60 for men and 55 for women. The retirement date is calculated by date of birth and gender.

= DATE (YEAR (C2) + IF (E2 = "male", 60, 55), MONTH (C2), DAY (C2))

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Yi Xuelong

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.