17 Useful Human Resources Formulas and Functions for Excel

Microsoft Excel isn’t a substitute for your core HR management system or advanced people analytics, but as a day-to-day tracking and ad hoc reporting tool for HR, it is hard to beat. Using human resources formulas and functions in Excel to answer questions and make better decisions will help you along your road to becoming a data-driven HR professional. It will also help you understand what your embedded analytics tools tell you.

Using Excel is easier, faster, and cheaper than relying on IT for reporting, and chances are your HR team has one or more people who are at least somewhat knowledgeable about it.

However, most users’ skills don’t go beyond simple tracking and using reports created by others. We have found that being the “Excel wizard” in HR can positively affect your employability and income.

Microsoft’s extensive documentation is written for everyday users. Excellent training resources and dozens of excellent books make it easy to learn—and for us, the learning never stops.

With that in mind, we are offering here some of the most common human resources formulas, tools, and functions in Excel. We hope you find them useful and that you become eager to learn more.

Contents
Working with dates
Working with pivot tables
Formatting tables
Common human resources formulas and metrics

Working with dates

Let’s start with some commonly used functions for managing dates.

Excel stores dates as sequential serial numbers beginning with 1, starting with January 1, 1900, at 00:00 AM. March 16, 2021 is 44271 because it is the 44,271^st day since the beginning of Excel time.

Values less than a day are stored as decimal fractions.

Excel has many ways for us to store, compare, and compute dates and times. There are sixteen standard formats for dates, and custom formatting rules give you even more control over the display. To choose a format, right-click on a cell or a list of dates and select one of the date formats or choose the Custom formats to see even more.

Before we start calculating dates, let’s see how we can enter today’s date—even if we don’t know what it is.

Using the TODAY() function

The TODAY function is a time-saving shortcut. It retrieves today’s date and time from your computer, so it updates automatically. Your worksheets automatically update when you open or change them. 

The function is handy for calculating things like age, time in service, or any case where the period you are using does not have a specific end. For example, if you want to calculate an employee’s age, you can subtract the employee’s birthdate from today. We start by formatting the column to display years instead of a serial number or date.

1. Right-click the Age column header the action panel will open.
2. Select Format Cells…
3. Custom opens the options; then you type “yy” (no quotes) in the Type field.
4. Click OK.

Finding the difference between two dates

Some of the things we do most frequently in HR measure the difference between two dates. We use those calculations for many reasons, including basics like age of employees, time in service, benefits eligibility, pensions, and seniority.

There are many ways to calculate the difference. How you calculate them depends on your company’s practices, industry sector, and country or region.

We’ll give you four that we find useful and easy to use:

DATEDIF function

Excel DATEDIF returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a “compatibility” function from Lotus 1-2-3.

=DATEDIF(start_date, end_date, unit)

Unit Values

• “y” years
• “m” months
• “d” days
• “ym” months, ignoring years
• “yd” days, ignoring years and months

We can calculate years, months, and days using these formulas:

To give us this result:

Here is the same method using years, months, and days in a single cell:

DAYS360 function

Our Gregorian calendar makes things difficult for accountants, so they invented the 360-day year with 30 days in each month to create a tidy framework.

Although the Securities and Exchange Commission (SEC) does not accept it for formal published financial accounts, the 360-day Commercial Year is widely used to simplify interest calculations and internal valuations in business worldwide. Two accepted standards are the US (NASD) method and the European method.

The syntax is:

=DAYS360(start_date, end_date, method)

where method indicates a 360-day standard: FALSE for the US method and TRUE for the European method.

• In the US method, if a starting date is the last day of the month, it becomes the 30^th day of the month. If the ending date is the last day of a month and the starting date is earlier than the 30^th day of a month, the ending day is equal to the 1^st day of the next month. Otherwise, the ending day becomes equal to the 30^th of the same month.
• In the European method, starting and ending dates on the 31^st of the month equal the 30^th of the month. How’s that for tidy?

Caution:

Dates must be either derived from another function or entered using the DATE format:

DATE(yyyy,m,d) where DATE(2021,3,11) is March 11, 2021.

If the start_date is after the end_date, the DATEDIF formula returns a negative number.

YEARFRAC function

If you want to calculate the difference between two dates as years and fractions of years, YEARFRAC will do it for you. The parameters for the function are the start date, end date, and basis, where the basis is 0 to 4:

Basis values

Here’s the function itself:
  =YEARFRAC(start_date, end_date,[basis])

If you use YEARFRAC and the TODAY() function, it will always be correct.

NETWORKDAYS function

NETWORKDAYS is a handy function for managing projects, schedules, or any time you need to calculate a span of workdays that includes holidays.

=NETWORKDAYS(start_date,end_date,holidays)

Here’s how we most often set up our holiday schedules. We create a worksheet named Holidays that contains the dates and names of holidays, then:

1. Select cell range $A$2:$A$10.
2. Click Define Name. The New Name window will open.
3. Type “Holidays” in the Name field.
4. Click OK.

Then, we can enter a NETWORKDAYS Function anywhere in a workbook like this:

Formatting tables

Before we begin working with lookup formulas, let’s discuss how to format employee tables, so your functions and procedures work consistently.

Every table should have a primary key. In structured query language (SQL), a column or set of columns is a unique identifier. In Excel, we want the primary key to be a single column that identifies each data row.

If you work with employee information, the key is almost always the Employee ID. Placing it the first (left-most) column in every worksheet that lists individual employees makes VLOOKUP, INDEX, MATCH, and INDEX-MATCH functions much easier to manage.

Format as Table function

We like to name our tables and arrays to make them easier to use. A quick way to do that is to use the Excel Format as Table function. 

1. Make sure your table is contiguous, meaning there are no gaps between columns or rows.
2. From the Home tab, click in the top left-most cell (A1).
3. Click on the Format as Table down arrow. The design panel will open.

4. Click on the design you want. The table form will open, displaying the table range and indicating whether your table has headers. You will want headers, so if you don’t have them, go back and reformat your table.

5. Click OK. Your table will be automatically formatted.
6. Give the table a name that makes sense to you. You can now use the table name in functions and formulas.

You can also use a keyboard shortcut to create tables.

1. Highlight your entire table.
2. Press Ctrl + T

You can then format it any way you like.

Filtering data

If you follow along in an Excel worksheet, you probably noticed that your tables automatically have filters. Filters are amazing time-savers when you want to slice and dice data. They can also be useful when you want to validate the data in a table.

Suppose you needed to follow up on performance appraisals that have not been completed. You have a report that lists all your employees with the appraisal status.

You could sort the worksheet by status and copy and paste the ones you need to follow up, but it is easy to apply a filter by selecting the status you want to follow. In this example, we want to find all the appraisals that had not been started.

1. Click on the filter drop-down icon.

2. Clear the form by click on the (Select All) checkbox.

3. Select Not Started, then OK.

Here is what your filtered table would look like:

You can use multiple filters in many combinations according to the data type in the column. Number filters have many combinations, as do dates.

Suppose we needed a list of people hired between January 1, 2016 and December 31, 2018.

1. Click on the Date of Hire column filter. A list of filter options will open.
2. Select the Date Filters option. You now get a long list of filtering options.

3. Select Between and fill in the date parameters.

Your report is ready:

Working with pivot tables

Of all the features, functions, and tools in Excel, pivot tables are among the most useful. They are dynamic summary reports you generate from a database, a table in a worksheet, an external data file, or an external database.

Check out our video to find out how you can use Pivot Tables for Excel!

The PivotTable function enables you to transform any data source into robust, flexible data summaries in a few clicks. You can then slice and dice, summarize and combine, and manipulate data in just about any way you can imagine.

We will show you how to create a report of termination reasons report by department. You can get a copy of the dataset we are using on Kaggle (mirror).

Here’s how to create a report from any worksheet:

1. First, we click in the top-left cell (A1) in a contiguous table of employee data. A good data sample will have the employee number, a value unique to each individual, in column A.
2. We click on the Insert menu.
3. Then, we click on the PivotTable icon. The Create PivotTable form will open.

4.  Excel automatically selects our entire table of data.
5. We select New Worksheet to tell Excel where to put the table.
6. Then, click OK.

7. A new tab will open with a blank pivot table on the left and the PivotTable Fields form on the right. Now, we do housekeeping first—renaming the tab so we can find it later.

8. In the PivotTable Fields form, drag Date of Termination to the Values section, giving us a numerical value for the table to count.

9. We also drag Department, Employee Name, Position, and Date of Term to the Rows section.

At this point, the table will start to take shape, but it will be a confusing mess. We’ll fix that now.

10. Click anywhere in the table, then click on design. The design menu appears.
11. Click on Report Layout.
12. We click on Show in Tabular Form. It starts to make sense, but we don’t need a subtotal in the Employee Name column.

13. Right-click in the Employee Name column and click on Subtotal “Employee Name” to uncheck it. We do the same with the Position column.

We now have a neat table showing the number of terminations and reasons for each department.

There is just a little cleanup to do. Some of the column names are a bit long and confusing. We can enter new names directly into the column headers.

One more thing:

Let us show the reasons why this tool is called a pivot table.

In the PivotTable Fields form, we grab the Department field and drag it to the Filters section to pivot the field from a row field to a filter where we can change the report to show a single department or any combination.

Now, we’ll try another pivot. We right-click on the Reason field, hover on Move, then click on Move “Reason” to Beginning.

We now have a report on reasons for termination.

Pivot table columns

If we wanted to see the reasons for the terminations summarized for each department, we could use columns to show the departments.

Let’s remove the report’s names and positions to reduce clutter, then drag the department to the Columns section. We now have a summary report, but it’s not very useful.

Let’s change the way we summarize the data. Right-click in the Grand Total cell and select Show Values As, then % of Grand Total.

After we use the Grand Total cell to format the numbers as a Percent with no decimals, we have a report that tells us something about where we might need an intervention.

We’ve given you only a quick introduction to the power of pivot tables. We hope it whets your appetite to learn more.

Browse our training courses to see how you can put pivot tables into action in people analytics. Once you master them, you will find them to be your best friend when someone in your team needs a quick answer.

Common human resources formulas and metrics

Although there are hundreds of formulas and functions that can help HR get things done, there are a few that stand out because they support important HR metrics.

Cost of benefits and employee programs

Calculating benefits (including all employee programs) helps us calculate the costs of benefits and calculate the total cost of employees for use in other internal metrics.

Benefits costs per employee

We calculate the cost of benefits as the ratio of benefits costs to an average number of employees.

benefits_cost = total_cost_of_benefits_and_programs ÷ average_number_of_employees

You can break this cost down by any period for comparisons.

Many companies compute separate ratios for hourly workers, salaried employees, and executives.

Benefits and programs as a percent of pay

We compute benefits to pay as a percentage ratio. 

benefits_ratio = cost_of_benefits ÷ total_pay 

Total employee cost

Once we have the benefits ratio, we can use it to calculate the total cost of employees.

total_employee_cost = (1 + benefits_ratio) × total_pay 

Revenue

In HR, we sometimes get so focused on cost that we forget to show our business impact. We recommend partnering with managers and leaders throughout the business to answer the most important questions when making a change or implementing an intervention. They are: What moved? By how much?

Revenue per employee

A quick answer to those questions is in Revenue per Employee. We get the top-line revenue from financial reports and divide it by the number of employees.

revenue_per_employee = average_number_of_employees_÷ top_line_revenue

Tenure and turnover

Tenure

You can get average tenure with an average function on a list of employees with Hire Date using a YEARFRAC function we explained above.

1. Format your list as a table.
2. Create a new column next to Hire Date named Tenure. Then, enter the formula in the top cell.
   =yearfrac([hire_date],today())
3. In the Number menu group, format the value using the comma icon (Accounting format).
4. In the Table Design tab, check the Total Row checkbox. The view will jump to the bottom row.

5. In the Tenure column, select the Average function.

Talent acquisition metrics

Recruiting costs are a critical business metric. You need to know how much time it takes and what costs to hire talent. Then, you can compare those costs against what you spend on employee engagement, retention, and development.

Time to fill a vacancy

The time it takes to source, engage, select, and hire a candidate is a critical factor in calculating recruiting costs.

An inefficient pipeline can cause costs to grow out of control, and the impact is much more than the cost of operating the recruiting function. Your people must cover the vacancy, and you may incur overtime costs. There will be additional stress on the people who cover it.

First, we define a table named “Holidays” to use it as a NETWORKDAYS function parameter. 

Cost per hire

Cost per hire includes covering the vacancy, the direct costs of advertising and hiring, and the cost of managers and recruiters in the process. Here’s a sample breakdown of the talent acquisition cost with respective HR formulas.

Yield ratio

To understand your talent acquisition function’s efficiency and effectiveness, you will need to measure how each step in the process functions. Only when you do that can you make informed decisions on what is or is not working.

The yield ratio is the percentage of applicants who make it to the next step of the process.

yield_ratio = applicants_at_step ÷ applicants_at_previous_step

Hiring velocity

Time to fill and hiring velocity are not the same. Time to fill only gives you the length of the entire process. Many companies are now beginning to measure how quickly the process moves at each step. Any delay at a critical time can lead to candidate ghosting. 

You can track velocity along with your Yield Ratio. Here are the formulas.

And the result. We calculated the total time to fill in the last column.

Over to you

We hope this quick overview has given you a taste for making data-driven decisions. We encourage you to learn by doing and apply these human resource formulas and functions. Ultimately, use your findings to guide and adapt your day-to-day operations and improve your HR strategy.

John W. McCoy

John W. McCoy retired from his career an HR professional, data analyst, and human capital management consultant in 2013. Since then, he has been feeding his fascination with technology, people, and work by writing for HCM software companies in North America and Europe.

Learn more

Related articles

Guides

What Is Workforce Analytics? Your 2024 A-Z Guide

Read more

Guides

OKRs vs. KPIs: The Key Differences & Use (With Examples) 

Read more

Guides

What is HR Analytics? All You Need to Know to Get Started

Read more

New articles

Guides

Generative AI in HR: Examples & How To Successfully Start Using AI

Read more

Guides

How To Advance Your HR Career With Human Resources Professional Development (In 2024)

Read more

Guides

The 21 Best Employee Incentive Programs [+ Real-Life Examples]

Read more