May 06

Recruitment, training & development, compensation & benefits, payroll, employee relations, staff retention, ... 

HR professionals are often seemed to have the easiest jobs in the company. 

Or is it?​

In reality, what goes behind the scenes are the endless amount of human resource information system (H.R.I.S.) inputs, preparing reports for the management, and spending face-time with employees just to get the day-to-day work done.

Here's some Excel magic to help you cut your day-to-day HR work from days and hours to just MINUTES.

Excel for HR Professionals - 7 Essential Functions

More...

Excel for HR Professionals:

7 Essential Functions That Will Cut Your Work From Hours to Minutes

7 Excel functions that are indispensable to HR professionals

Infographics - Excel for HR

HR meets Excel

In her book, Vault Guide to Human Resources Careers, Susan Strayer describes HR as "the glue that holds people and an organization together". 

This "glue" really has many tasks to execute on a daily basis.

To cope with the​ heavy workload, some companies outsource part or all of their HR functions, while some uses H.R.I.S software. 

HR job scope

However, the majority of the Small and Medium Enterprises are not using any specialized software to track employee details​ but using Microsoft Excel.

​Here comes the issue:

HR professionals are already so busy with their HR work. But without investing time to learn Excel, they often are not aware of the magic that Excel can bring into their work.

The endless cycle of working hard and late thus continues.​ 

Today we will mention 7 essential Excel functions that will help HR professionals cut their work from days and hours into mere MINUTES.

Ready?​


#1: TODAY

Many times, HR professionals are required to calculate dates with reference to today's date. 

Age of employee, length of service, etc. are all referenced to today's date. ​

The function TODAY recalculates itself so that you will always have today's date in formulas.​

If today is 5th Ma​y 2011, TODAY gives you 5th May 2011. If you open the Excel worksheet the next day, TODAY then gives you 6th May 2011.

To use this function, simply type TODAY().​

Today
=TODAY()

TODAY is a volatile function; it recalculates whenever you as a user takes an action (e.g. change value of a cell, insert/delete a row or column). In a very very large Excel spreadsheet, this may slow down your worksheet considerably. If so, consider using VBA to hard-code today's date in extremely large spreadsheets.


#2: DATEDIF

To find out the difference between 2 dates, let me introduce you to the function DATEDIF. DATEDIF calculates the number of days, months or years between 2 dates

​The syntax for DATEDIF is =DATEDIF( start_date, end_date, unit)

​We have a few options for unit:

  • ​"y" - number of completed years
  • "m" - number of completed months
  • "d" - number of days
  • "ym" - after ignoring years, the number of completed months
  • "yd" - after ignoring years, the number of days

You may encounter these two error messages at times. Here are the possible causes:

  • ​#NAME? - check if you have included " " around the unit
  • #NUM! - check that start_date is earlier than end_date.

Let's try to apply.

Say if one of your employee is born on the 3rd December 1984. Find his age this year.

*hint: use TODAY() as end_date.​

Find the age

Yup, that's my current age, 32 since today is 2 May 2017 which is before my date of birth.

If you want to consider everyone who is born in 1984 to be 33 years old​, try this:

​=DATEDIF( DATE(YEAR(E1),1,1), TODAY(), "y")

We set the date of birth to be 1 Jan of that year, so that everyone born in that year will be 33 years old.

Another example, ​to find the length of service of an employee who is employed on 15 Mar 2010.

Anniversary in Years

Interestingly, Excel provided the function DATEDIF to be compatible with Lotus 1-2-3 workbooks which were popular during the 1980s and 1990s. The DATEDIF function cannot be found in Excel's in-built Insert Function feature.

For more information on DATEDIF, refer to Microsoft's explanation of this function. 


#3: EDATE

Are you free for a date? *laughs*

​EDATE helps you to set future dates based on the number of months. 

3-month probation period? 1-month resignation notice period? ​

No worries, let EDATE sets it up for you:

The syntax for EDATE( start_date, months)​

​In our case, we will use =EDATE( probation_date, 3) for 3-month probation.

Calculate Probation using EDATE

Try it for yourself for a 1-month resignation notice period.


#4: NETWORKDAYS.INTL

Often we may need to figure out the number of working days between two dates.

How do you do that?

At this time, many HR professionals will be taking out their desktop calendars and counting the days.

1, 2, 3, ... someone interrupted them and they start from 1 again.

How do we also account for public holidays? If you are using Excel 2010 and later versions, NETWORKDAYS.INTL is here for the rescue!

Step 1: Create a list of public holidays similar to the picture below, with two columns - Day and Date. Highlight the data and hit Ctrl + T to format as an Excel table.

Public Holidays

Step 2: Under {Table Tools} Design | Properties | Table Name, rename the table name as PublicHolidays.

Public Holidays 2

Step 3: Time to conjure some Excel magic. 

The syntax for NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays])​

The format for weekend is a series of 7-digit number starting from Monday to Sunday, with 1 for not working and 0 for working. So for an employee not working on Saturdays and Sundays, their weekend will look like "0000011".

Step 4: Putting it together​:

=NETWORKDAYS.INTL( E1, E2, "0000011", PublicHolidays[Date])
Number of Workdays

Indeed, 22nd, 26th and 27th Dec 2017 are working days. 

Since a large majority of employees worldwide are not working on Saturdays and Sundays, Microsoft Excel has made the default weekend settings to be "0000011" if you omit it. So for the same example above, you can also use:

=NETWORKDAYS.INTL( E1, E2, , PublicHolidays[Date])​

For holidays, it is very easy to maintain using the Excel table. When the new year comes, e.g. 2018, just continue to add the Day and Date information below the existing table, and the data will be automatically included in the calculations. 

For more examples, please see Microsoft's article on NETWORKDAYS.INTL.​


#5: WORKDAY.INTL

With NETWORKDAYS.INTL, we can calculate the number of working days between 2 dates.

And now, meet its brother ​WORKDAY.INTL

WORKDAY.INTL uses a start_date and number of work days to calculate the end_date. Not very useful it seems.

But its power shines in the HR industry where you can use a negative number of work days to go count backwards from the date supplied to Excel.

The syntax for WORKDAY.INTL( start_date, days, [weekend], [holiday])

Sounds confusing? An example is easier to illustrate the power of WORKDAY.INTL.

Supposed we have an employee whose last day is 26 Dec 2017. He has 7 days of annual leave left and the management has agreed to offset the 7 days of annual leave from 26 Dec 2017.​

So what is his actual last day in the office?

=WORKDAY.INTL( E1, -E2, , PublicHolidays[Date])​
Offset annual leave from notice period

Verify it yourself using a desktop calendar. Keep in mind of the Saturdays and Sundays, and 25 Dec 2017 being a holiday.

I'm sure you will be blown away by WORKDAY.INTL!​


#6: COUNTIF

Many times HR will be required to produce monthly reports. And preparing reports can typically take quite a long time.

Well, no more. 

We will go through 2 functions, COUNTIF and SUMIF that will speed up your reporting from days and hours to mere minutes!

Step 1: Using the example below, create a HR personnel list, with columns - Name, Department, and Salary. Highlight the data and hit Ctrl + T to format as an Excel table.

Step 2: Under {Table Tools} Design | Properties | Table Name, rename the table name as HRlist.

HR personnel list

Step 3: To count the number of employees that match a criteria, we use the COUNTIF function. For example, to count the number of Finance employees, we can use:

=COUNTIF( HRlist[Dept], E2)

We are counting the items in the Dept column and checking them against the criteria "Finance" found in cell E2.

And for number of employees in HR and Sales departments, we just need to copy cell F2 and paste into cells F3 and F4.

Counting the number of employees in a department

​Report is half done, now moving on to the last item...


#7: SUMIF

Meet the twin of COUNTIF. Hello, SUMIF!

So COUNTIF is to count items based on a criteria, then SUMIF is to sum the items up based on a criteria​.

After counting the headcount for each department, we are left with summing up the salary expense of each department.

How do we do that?

=SUMIF( HRlist[Dept], E2, HRlist[Salary])​

We are asking Excel to check the Dept column to find the records matching "Finance" in cell E2. If so, sum up the corresponding salary. 

Summing the salary expense of a department

Wow that only takes an instant! 

So are you ready to defeat HR workload with your new found Excel powers? Let me know which is your favourite function in the comments below.