How to create payroll formulas for paid time off in #Sage300CRE

by Kyle Zeigler, Sage Senior Certified Consultant

New federal and state requirements for PTO

Creating formulas in Sage 300 CRE can be daunting for even the most experienced user, and payroll formulas are often the most complex.  With expanding Federal and State legislation requiring paid sick leave, automatic accrual of hours for this or any other type of paid time off (PTO) is a worthwhile objective.  But coming up with the formulas to automate the accrual becomes a challenge.

Beginning January 1, 2017, Executive Order 13706 requires certain companies that contract with the Federal Government to provide their employees with up to seven (7) days of paid sick leave annually.  In addition, more and more states are following the Federal example and establishing paid sick leave requirements for employers in their state.  For instance, beginning July 1, 2017, employers in Arizona must accrue a minimum of one (1) hour of earned paid sick leave for every 30 hours worked.  There is more to the Arizona legislation, so users in this state, as well as other states with similar requirements, should become thoroughly familiar with all aspects of the laws in their state requiring paid sick leave.

Types of formulas

In many cases, more than one formula may be needed to accrue any type of PTO, including paid sick leave.  Separate formulas are often used to provide the following variables for calculation:

  • Employee anniversary date
  • Employee length of service
  • Tests for Pay Group or department
  • Total employee YTD hours accrued
  • Total employee YTD hours taken
  • Total regular and overtime hours worked during the pay period

For example, a PTO accrual formula that ensures the calculation occurs only on the first check in a pay period, checks the employee Pay Group where some employees are paid weekly and others are paid biweekly, and then calculates PTO hours based on formulas for the Pay Group might be written:

IF (Check Sequence [PR Check] = “First” AND Pay Group (PR check) = “BIWEEKLY”, Sick Accr BW (Public Formula), IF(Pay Group (PR check) = “WEEKLY”, Sick Accr WK (Public Formula)))

Note that this formula contains two additional formulas that were created separately and then combined in one relatively complex formulas in an “If – Then – Else” type calculation.  Selecting the right records to use in each of the formulas is key to the success of the calculations, and logical sequencing is critical.

If your head hurts just reading this, don’t despair!  The Help Topics in PR > Help > Formulas give great information and examples.  And even more examples are available in the Sage Knowledgebase.

It’s important to test the formulas once created before using them in a live payroll.  This is easily accomplished by linking the final formula into the Pay setup for the accrual, adding the Pay to an employee record, marking it as automatic, and then creating and processing a sample check using Enter Checks.

And as always, if you’d like a little expert help getting your paid sick time and other PTO accrual formulas set up and working in your system, please call Ledgerwood Associates at 480.423.8300.

Want help from a Certified Sage Consultant with Payroll formulas?