In last weeks post I talked about where the employee's age appears and how it is stored in the software.  This week, let's take a look at age related functions and how they are used when setting up benefits with expression builder.

Functions used to calculate age when setting up benefits.

When setting up a rate table or a benefit plan, there are several functions that can be used to calculate someone’s age.  These are used in Expression Builder.

AGE() – Calculates the employee’s age.

YEARSOLD() – Calculates the employees age as a whole number. The result will be a whole number rounded down.

AGESPOUSE() – Calculates the spouses age. 

Calculating age as of a specified date.

Now that we know there are functions that can be used in Expression Builder to calculate a persons age, how do we calculate their age as of a certain date?  To do this, we will need to insert information inside the parenthesis ().  One way of doing this is YEARSOLD(Pe.P_Birth,{01/01/2022}).  This will calculate the employees age as of January 1, 2022.  The two dates inside the parenthesis are subtracted.  When working with a date field in Expression Builder, the date is surrounded by french braces or sometimes referred to as french brackets { }.

To calculate a spouses age using AGESPOUSE() insert the date inside the parenthesis that you want it calculated as of.  For example, your benefit plan may require the rates to be calculated for the spouses age as of January 1, 2022.  The expression would be AGESPOUSE({01/01/2022}).   

This is great stuff!  Now let's take things to the next level.  You may be thinking I don't want to have to remember each year to go in to the benefit plan or rate table and modify the expression to the new year ie. 2023.  In the following example, the benefit plan calculates the employees age as of January 1st of the current system year. 

IIF(CTOD("01/01/"+STR(YEAR(DATE())))<=DATE(),INT((CTOD("01/01/"+STR(YEAR(DATE())))-TtoD(PE.P_BIRTH))/365.25), INT((CTOD("01/01/"+STR(YEAR(DATE())))-TtoD(PE.P_BIRTH))/365.25)-1)

Looks pretty complicated, right?  Well, it is, but the good news is someone has already come up with the expression for us.  In fact, I got this expression and the others above from the Sage Knowledgebase. Where it gets complicated is when we need to consider leap year /365.25)-1 and use the current month and day from the computers system date.  To use this expression, replace each instance of 01/01 with the month and day you would like the employee's age calculated as of.

IMPORTANT! No matter which expression you decide to use, ALWAYS test the expression first and make sure to back up your data.

Next week, we will find out how we can learn about expressions.  If you have any questions, please respond to this post.