Excel for HR Professionals:
7 essential functions that will cut your work from hours to minutes

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.

No Time to Read?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

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.

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 nights 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.



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 May 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 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.


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.

=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


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.


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.


Nope… I’m not asking you out for a date.

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:

=EDATE(start_date, months)

In our case, for 3-month probation we will use:

=EDATE(probation_date, 3)


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.


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

Step 3: Time to conjure some Excel magic.

=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])

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.


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.

=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])

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!


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.


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.

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


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.



Wow that only takes an instant!

There, now you are ready to defeat HR workload with your new found Excel powers.

If you like this article, then it will be helpful to check out how to perform calculations with dates and what’s actually behind the scenes of how Excel handles dates.

And if you’re based in Singapore, check out our series of Excel courses in Singapore to get yourself and your colleagues ahead today!

No Time to Read?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

Get The Defeat Excel Handbook Today!

50+ Frequently asked how-tos

No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.

PDF Format

Easily search through the book to find the solution you need. Plus it works offline too.

Effective solutions

No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.