17 Useful Human Resources Formulas and Functions for Excel

You are here:

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

Related (free) resource ahead! Continue reading below ↓

51 HR Metrics cheat sheet

Data-driven HR starts by implementing relevant HR metrics. Download the FREE cheat sheet with 51 HR 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,271st day since the beginning of Excel time.

Values less than a day are stored as decimal fractions.

Date function

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.

Related online course ahead! Continue reading below ↓

Online, Self-Paced & Globally Accredited

HR Metrics Dashboarding
Certificate Program

Advance your HR career by mastering data-driven HR in just 10 weeks with 4 hours of studying a week.

Download Syllabus

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.

Format cells
  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.
TODAY function

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)

How T-Shaped are You?

The most successful HR professionals in today's digital business environment have a T-shaped competency profile. Take the free assessment now!

Start assessment

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:

DATEDIF function

To give us this result:

DATEDIF function results

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

DATEDIF function results

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 30th day of the month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending day is equal to the 1st day of the next month. Otherwise, the ending day becomes equal to the 30th of the same month.
  • In the European method, starting and ending dates on the 31st of the month equal the 30th 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

0 (or omitted) US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

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

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

YEARFRAC function

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.
NETWORKDAYS function

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

NETWORKDAYS function results

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.
Table
  1. 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.
Table data
  1. Click OK. Your table will be automatically formatted.
  2. Give the table a name that makes sense to you. You can now use the table name in functions and formulas.
Table name

You can also use a keyboard shortcut to create tables.

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

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.
Filter drop down
  1. Clear the form by click on the (Select All) checkbox.
Filters
  1. Select Not Started, then OK.
Filters use

Here is what your filtered table would look like:

Filtered table

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.
Date filters
  1. Select Between and fill in the date parameters.
Filter parameters

Your report is ready:

Filtered report

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.

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.
Pivot table
  1.  Excel automatically selects our entire table of data.
  2. We select New Worksheet to tell Excel where to put the table.
  3. Then, click OK.
Pivot table worksheet
  1. 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.
Pivot table creation
  1. In the PivotTable Fields form, drag Date of Termination to the Values section, giving us a numerical value for the table to count.
Pivot table fields
  1. We also drag Department, Employee Name, Position, and Date of Term to the Rows section.
Pivot table fields

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

  1. Click anywhere in the table, then click on design. The design menu appears.
  2. Click on Report Layout.
  3. We click on Show in Tabular Form. It starts to make sense, but we don’t need a subtotal in the Employee Name column.
Pivot table layout
  1. Right-click in the Employee Name column and click on Subtotal “Employee Name” to uncheck it. We do the same with the Position column.
Pivot table filters

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

Pivot table results

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.

Pivot table cleanup

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.

Pivot table edit

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

Pivot table edit

We now have a report on reasons for termination.

Pivot table report

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.

Pivot table columns

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.

Pivot table summary report

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.

Pivot table 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.

Pivot table final report

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.
Human resources formula for tenure
  1. In the Tenure column, select the Average function.
Tenure average

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.

Human resources formula for time to fill

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.

Cost Formula
Cost of covering a vacancy salary X days_to_fill + allocated overtime
Recruiter time to source, screen, and interview candidates (Recruiter_hourly_pay + benefits_ratio) × hours
Manager time to interview, evaluate, and select candidates (Manager_hour_pay + benefits_ratio) × hours

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.

Human resources formula for hiring velocity

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

Hiring velocity results

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.

Are you ready for the future of HR?

Learn modern and relevant HR skills, online

Browse courses Enroll now