Using date math for more accurate accounting

Introduction

Date math might sound like the latest algorithm on Hinge or Tinder, but it’s actually a useful granular accounting technique that helps match your ledger with the realities of your business.

You probably know by now that integrations in Runway can work wonders in getting you up and running fast — but sometimes you might need a bit more granular detail than what’s provided to represent your data accurately for things like payroll or headcount. That’s where features like date math come in handy.

Date math allows you to account for more granular detail, such as a partial month salary for an employee who started mid-month, in your ledger.

This article will help you get started using date math to more accurately account for this kind of detail in your Runway modeling.

Not looking for date math? Here are some other related topics:

Writing formulas

Getting started

Granularity in payroll data is important for many reasons, particularly when importing data from HR systems like Rippling or Gusto, where the data is usually in an annual salary format.

Simple division, like dividing an annual salary by 12 to break it into a monthly view, doesn’t give us an accurate monthly view of an employee's salary when, for example, the employee has started or ended their tenure in the middle of the month.

The good news is that Date Math can provide the level of detail you need for more accurate accounting standards.

Using Date Math in Runway:

We’ll use employee compensation for this walkthrough example of how to use Date Math in Runway.

Create a prorated salary formula for mid-month starters:

Before we begin, make sure that ‘Monthly Salary’ is a number field in your database in Runway. We'll need this to create a formula for pro-rated salary, which uses a logical operator.

Start with an if() statement to check if an employee's start date coincides with the current month.

Use the dateDiff() function to calculate the difference in days between the start date and the end of that month.

Divide the result by the total days in the month (using the DAYS IN MONTH function) to get the prorated amount (or if you have employee monthly salary already calculated, select “this employees monthly salary.”)

Multiply this prorated fraction by the employee's monthly salary (annual salary divided by 12).

Adjust the formula for mid-month terminations:

Add another if() statement to the formula to handle cases of employees leaving mid-month.

Set up a structure similar to the starter’s formula but reference the termination date instead.

Calculate the date difference between the start of the month and the termination date.

Divide this by the total days in the month and then multiply by the monthly salary for the prorated amount.

Calculate full-month salaries:

Add a final argument to the if() statement to cover scenarios where the employee has worked the full month.

In these cases, simply divide the annual salary by 12.

Finalize the formula:

Ensure that the formula now accounts for all scenarios: mid-month starts, mid-month terminations, and full months.

Verify the formula by checking if employees who started or ended mid-month, like the example given, have their pay correctly prorated.

Ensuring Accuracy in Payroll Calculations

With the steps outlined above, you now have a system for making your payroll data reflect the exact working days of each employee, ensuring fairness and accuracy in your accounting.

By leveraging date math in Runway, you can easily transition from a broad approach to a more granular one, accommodating unique start and end dates within the payroll cycle. This method not only refines the payroll process but also enhances the overall financial management of your company by providing precise salary calculations.

Remember, attention to detail in payroll not only helps maintain financial integrity but also builds employee trust and satisfaction.

Troubleshooting

Having issues with date math or formulas?

I don’t understand Runway’s formulas

Runway formulas are designed to be human-readable. Check out our article on writing formulas.

Additional Resources

Get in touch with a Runway team member