How to automatically calculate payroll using Airtable

Introduction

Would you like to automate the payroll procedure for your company and save hours of mundane work each month? Of course you do! 🙂

In today’s article I will show you how to do exactly that using our favorite tool, Airtable.

Setting up your tables

Here at MemberFix we pay our team once a month, on the 2nd of each month.

To do that, we first need to calculate the total number of hours that each team member spent working that month. Additionally, since we offer our team members a certain number of paid days off each quarter, we have to factor that number into the equation as well.

The Payroll table itself—which is where you’ll see the calculated amount owe to your team each month, where you indicate payment statuses, and so forth—is the only table that incorporates math. 

Don’t worry! I am going to explain each formula and how it works. 🙂

But first we need to create the tables from which we pull the data needed to calculate our payroll numbers.

I recommend setting up the following 4 tables to start:

• Team – Stores important data about each team member
• Timesheet – Keeps all records of our work (related: how to track time with Airtable)
• Schedule – table to check used paid day offs (related: how to manage your team’s schedule with Airtable)
• Payroll – table that calculates salaries for each team member using linked records from the first tables

The Team table

For the whole process to run we need to make some preparations. We’ll start from the Team table.

Our Team table looks like this:

The fields which are required from this table are:

• Name (Single line field) – Team member’s name
• Base hours (Duration field) – The number of hours required by the team member to work in a month. This will be used to calculate overtime and “undertime” (more on that below).

Initially, we also used the Hourly Rate field but then we dropped it. I’ll explain why in a moment…

But first let’s look at our Payroll table:

Here’s a brief overview of the fields in this table:

• Date (Date field) – Date when payment has been calculated
• Name (Link to another record field) – Linked record from Team table
• Month (Single select field) – Payment month
• Paid Offs (Number field) – The amount of paid day offs taken this month
• Hours worked (Duration field)  – The number of hours logged for this month
• Overtime (Formula field) – The number of hours worked that exceed base hours value
• Undertime (Formula field) – The number of hours left to reach the base hours amount
• Rate (Number field) – Current hourly rate for the person
• Base Hours (Lookup field) – Linked field: Name, lookup field in the linked table: base hours, this one lookup for Base Hours field in Team table for certain person
• Payment (Formula field) – The amount to be paid to the person
• Status (Single select field) – Status of the payment (On Review, Processing, Paid, Payment failed, Confirmed to pay)
• Comments (Long text field) – Any comments relevant to this payment

Now let’s go in detail through each field:

1 – Hours worked

This is 1 of only 3 fields in which we have to manually enter values in this Payroll table.

We tried to find a way to calculate the hours worked value automatically but decided it was too much work for too little reward. 

Of course, you could create a completely automated solution using 3rd party software like Zapier. But since it takes just a few minutes to enter these value manually we decided to skip it. 

Note that if you have a large staff (in the tens or beyond) it might be worth automating. But that’s for another article. 🙂

In any case, we will need our Timesheet table for this part:

In order not to interfere with our team members’ personal views, we created a separate grid view called Payroll calculation view.

Then we hid all of the fields except for the required fields.

Now we need to apply proper filtering. Let’s use me (Viktor) as an example and the month of May.

In this case, the filters will look like:

To get the number you are looking for you will need to set following filters:

WHERE Team member CONTAINS <Name of your team member>

AND

Start Date IS ON OR AFTER <First day of the month in question>

AND

Start Date IS ON OR BEFORE <Last day of the month in question>

Make sure that you have selected ‘is on or after’ and ‘is on or before’. Otherwise not all the time logged for the month will be counted.

After you are done with filters you will see a sum of all hours logged for the selected period of time at the bottom of the page. In our example, it is 153:03 hours.

This is exactly the number you will need to enter into Hours worked in the Payroll table. Now you just need to change Team the member filter to get the numbers for each of your team members. 

And next month you’ll change the dates and do it all over again!

2 – Paid offs

The Paid offs field is the second of three fields where we need to manually enter the values.

To do that we simply take the data for it from our Schedule table:

You need to apply almost the same filters as in the Timesheet table when you were calculating the number of hours worked with the sole difference that instead of the Team member’s name you will select the Status field (in our case the value we’re looking for is called Paid Off).

And if you group the fields by Team Member name you will get all of the data already counted for you and neatly organized.

Notice that we have a Paid Offs – Grid View view preconfigured so we can just go into this view, adjust the dates, and get the numbers when it’s time to meet our payroll.

3 – Rate

This is the last field whose values we have to enter manually.

Some time ago we used a Lookup field type to pull each team member’s hourly rate from the Team table.

But it turned out to be unreliable because Rate is not a constant value. People get promoted and their rates change. So if you rely on a lookup field from another table, and then change the value in that table – all of your previously calculated data at the old rate will be re-calculated for the new one.

Since the Payroll table is not merely used for calculation but also to store your payroll history, we replaced the Lookup field from that column with a simple Number field.

4. Overtime

We pay our team members overtime at a rate of 125% of their prorated hourly rate.

For the sake of simple math, let’s suppose a team member is required to work 80 hours per month. Let’s say his hourly rate is $20/hr. And let’s then suppose that he winds up working 100 hours on a given month. 

• 80 hours per month
• $20 per hour
• Worked 100 hours 
• 20 hours of overtime
• Overtime rate of $25/hr

For the first 80 hours our team member earn $20/hr. This results in a product of $1600 at the end of month.

Every hour he then works OVER that 80 hour requirement is considered overtime, and is paid at 125% of his normal hourly rate. In this example, the overtime rate will be $25/hr, 125% of $20 is $25.

You with me? 😉

We calculate this value using a Formula field and the following formula:

IF({Hours worked}+{Base hours}/20*{Paid Offs (days)}>{Base hours},{Hours worked}+{Base hours}/20*{Paid Offs (days)}-{Base hours},0)

(You can use logical functions in the formula along with the math in any combination).

The formula above means:

IF the number of {Hours worked} PLUS a number of Paid days off taken (re-calculated to hours) are GREATER THAN the {Base hours}, then SUBTRACT the {Base hours} value from the SUM of {Hours worked} and the number of Paid days off taken (re-calculated to hours) and DISPLAY the result. Otherwise, show 0.

It is absolutely crucial to use Fields (or at least values) of the SAME type and format when employing formulae. In our case these fields are Duration fields in the format hh:m.

We have 1 field that is not of Duration type so we need to convert the values to a common type.

That’s exactly what we’re accomplishing with this portion of the formula:

{Base hours}/20*{Paid Offs (days)}

Since our team has 20 working days per month, each Paid day off is equivalent to a certain number of worked hours that the company has to pay them for. This is based on each team member’s Base hours (this value changes for different team members depending on our work arrangement).

5. Undertime

This field uses the same math as the Overtime field except it is reversed:

IF({Hours worked}+{Base hours}/20*{Paid Offs (days)}<{Base hours},{Base hours}-{Hours worked}-{Base hours}/20*{Paid Offs (days)},0)

6. Payment

This is the most interesting field in this table (depending on your definition of “interesting”, of course)! ?

The formula looks like this:

({Hours worked}-Overtime)/3600*Rate+Overtime/3600*Rate*1.25+{Paid Offs (days)}*{Base hours}/20/3600*Rate.

To make it easy to understand let’s divide this formula into 3 parts:

1 – ({Hours worked}-Overtime)/3600*Rate – Payment for hours worked minus overtime. You may be wondering what the heck that 3600 value is doing there. Well, the reason is that Airtable keeps the Duration values in seconds, and we keep the Rate value as $X per hour.

So before we can do any calculations we need to convert time values from seconds to hours by dividing by 3600.

2 – Overtime/3600*Rate*1.25 – We pay all extra hours at 125% of the team member’s hourly Rate. Such a record allows us to omit logical expressions since any value multiplied by 0 still will be a 0. 

That middle school math coming back to you yet…? 😉

3 – {Paid Offs (days)}*{Base hours}/20/3600*Rate – Paid day offs are to be paid as a number of hours you would work at your usual day. We have been using the {Base hours}/20*{Paid Offs (days)} value already for our Overtime calculation, so here we just need to extend it by adding a conversion from seconds to hours and multiply it by the team member’s hourly Rate.

At first glance it may appear that we should have been using logical expressions rather than formulas. But in our case, it uses a core math rule – multiplying by 0 gives 0 as a result, and if you add a 0 to any value the value does not change. Therefore, it is a shorter formula that also happens to be easier to read.

Elementary, my dear Watson. 🙂

It’s important to point out that our founder spent hours each month—in asynchronous batches mind you—to manually calculate our team’s payments and send them out. Not only is this a particularly mundane form of work for somebody as handsome and creative as Vic, it was inaccurate and prone to mistakes. Not cool for our team who work their butt off and deserve all they earn, and not a shilling less.  

Also of note is that we switched from paying team members ad hoc to the convention used throughout Eastern Europe—where most of our team is based—of paying once monthly on the 2nd of the month.

It took a pretty healthy effort to concoct and refine this system. But once we nailed it, we instantly started to save a lot of time each month, automated a vital process, made it more accurate, and reduced headaches for our team. 

Last but not least, I want to highlight our amazing team members who willingly accepted a smaller monthly payment for one month so that we could make the transition to our new payroll system without mucking up the numbers or paying unnecessary fees. <3

How do YOU use Airtable in your business?

Tell us in the comments below.