Formulas | Formula library

The formulas in this library have been contributed by Sage People implementers and others working in customer projects over time. New contributions are welcome. The library continues to be updated and refined, so please help us by:

• Letting us know if there is a problem with any formula in the library.
• Continue to contribute formulas you have created that others could find useful.
• Providing feedback.

The formulas in the formula library are intended to work as examples and starting points for you to build on. Many formulas use additional custom fields not present in the managed packages or in the standard Sage People initial build. Always test the formula.

All product areas

Calculate the working days between date/time fields

• Find and replace CreatedDate with the date/time field you want to use as the starting point

• Find and replace ClosedDate with the date/time field you want to use as the end point for the period

(CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
        0 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
        1 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
        2 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
        3 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
        4 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
        5 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
        6 , CASE( MOD( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
        999)
        +
        (FLOOR(( DATEVALUE(ClosedDate) - DATEVALUE(CreatedDate) )/7)*5) )
        -1
        +(DATETIMEVALUE(DATEVALUE(CreatedDate)) - DATETIMEVALUE(CreatedDate)
        +DATETIMEVALUE(ClosedDate) - DATETIMEVALUE(DATEVALUE(ClosedDate)))

Format a numeric salary for U.S. context

This example uses the Amount field from the Salary object for a formula field in the Employment Record. You can modify the example to use a different field, such as fRecruit__Start_Salary__c field.

The formula formats the value as follows: 

• adds the currency symbol ($) before the numeric value

• uses commas (,) as the thousands separator

• displays 2 decimal places

• adds parenthesis if the amount is negative

"$"+ 
IF(fHCM2__Current_Salary__r.fHCM2__Amount__c < 0, "(", "") & 
IF(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) >= 1000000, 
TEXT(FLOOR(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) / 1000000)) & ",", "") & 
IF(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) >= 1000, 
RIGHT(TEXT(FLOOR(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) / 1000)), 3) & ",", "") & 
RIGHT(TEXT(FLOOR(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ))), 3) & "." & 
IF(MOD(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c) , 1) * 100 < 10, "0" & 
TEXT(ROUND(MOD(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) , 1), 2) * 100), 
TEXT(MIN(ROUND(MOD(ABS(fHCM2__Current_Salary__r.fHCM2__Amount__c ) , 1), 2) * 100, 99))) & 
IF(fHCM2__Current_Salary__r.fHCM2__Amount__c < 0, ")", "") 

Format a number field for use in a PDF template

This example for a formula field can be used to format a number, such as the Amount in the Salary record, for use in PDF template. The example uses a custom field Base Compensation.

IF(Base_Compensation__c >= 1000000, 
TEXT(FLOOR(Base_Compensation__c / 1000000)) & ",", "") & 
IF(Base_Compensation__c >= 1000, 
RIGHT(TEXT(FLOOR(Base_Compensation__c / 1000)), 3) & ",", "") & 
 
RIGHT(TEXT(FLOOR(Base_Compensation__c )), 3)& "." & 
IF(MOD(Base_Compensation__c , 1) * 100 < 10, "0" & 
TEXT(ROUND(MOD(Base_Compensation__c , 1), 2) * 100), 
TEXT(MIN(ROUND(MOD(Base_Compensation__c , 1), 2) * 100, 99))) & 
IF(Base_Compensation__c < 0, ")", "") 

Split picklist value with number and text into 2 formula fields

This example shows how you can split a picklist value containing text and numbers into 2 separate fields, one containing the text element and the other the number element. Typically, such text and number combinations are used in department names or cost centers. For example, if you have a picklist field for cost centers, and one of the values is 10200002 - Marketing, you can split it into a field holding the number value 10200002 and a field holding the text value Marketing.

The following examples use Dept_Number_Team__c as the picklist field holding the value, replace it with the name of the field you want to split.

The examples assume the length of the number portion is fixed at 8 characters, and that the number and text value are separated by a consistent number of characters (3, including two spaces and a hyphen), so the text portion starts after 11 characters.

LEFT( TEXT( Dept_Number_Team__c ) , 8) 

MID( TEXT( Dept_Number_Team__c ), 11, 50)

Roll up values of multiple fields into a text field

This example can be used for a formula field to roll up the values of multiple fields into a single field. The formula field can be used in email templates, for example. For more information on how to use the formula field in email templates, see Email template formulas.

The example formula uses custom rollup fields.

IF(Core_Ben_Critical_Illness__c > 0,'Critical Illness' & BR(),'') &
    IF(Core_Ben_Dental_Insurance_CF__c > 0,'Dental Insurance CF' & BR(),'') &
    IF(Core_Ben_Duvet_Days__c > 0,'Duvet Days' & BR(),'') &
    IF(Core_Ben_Income_Replacement_Scheme__c > 0,'Income Replacement Scheme' & BR(),'') &
    IF(Core_Ben_Medical_Cover_Single__c > 0,'Medical Cover Single' & BR(),'') &
    IF(Core_Ben_Medical_Cover_Single_Family__c > 0,'Medical Cover Single Family' & BR(),'') &
    IF(Core_Ben_Medical_Cover_Single_Married__c > 0,'Medical Cover Single Married' & BR(),'') &
    IF(Core_Ben_Medical_Cover_Single_Parent__c > 0,'Medical Cover Single Parent' & BR(),'') &
    IF(Core_Ben_Sabbatical__c > 0,'Sabbatical' & BR(),'') &
    IF(Core_Ben_Travel_Insurance_CF__c > 0,'Travel Insurance CF' & BR(),'')

Check specified fields have values

The following example is for a formula field to displays either N or Y depending on if the fields detailed in the formula have a value or not. The fields used in the example are a mix of managed package fields and additional custom fields.

This kind of formula can be used to ensure that required fields are completed for an integration Payflow file.

IF ( 
    ISBLANK( fHCM2__Unique_Id__c ) || 
    ISBLANK( fHCM2__First_Name__c ) || 
    ISBLANK( fHCM2__Surname__c ) || 
    ISBLANK( fHCM2__Manager__c ) || 
    ISBLANK( LE_Legal_Entity_Number__c ) || 
    ISBLANK(TEXT( Geo_Code__c )) || 
    ISBLANK( CC_Cost_Center_Number__c ) || 
    ISBLANK( PROD_Product_Number__c ) || 
    ISBLANK( fHCM2__Job_Title__c ) || 
    ISBLANK( fHCM2__Current_Employment__r.fHCM2__Work_Location__c ) || 
    ISBLANK( LE_Paid_by_Country__c ) || 
    ISBLANK( fHCM2__Current_Employment__r.fHCM2__Start_Date__c ) || 
    ISBLANK (TEXT( fHCM2__Employment_Status__c )) || 
    ISBLANK (TEXT( fHCM2__Current_Employment__r.Job_Code__c )) || 
    ISBLANK (TEXT( fHCM2__Current_Employment__r.fHCM2__Basis__c )) || 
    ISBLANK( Employment_Status_Effective_Date__c ) 
    , 
    "N", 
    "Y" 
    )

Trigger Flow when an entered date value changes

This example is to set a condition for a Flow so it sends a notification when previously entered Last Working Date changes, but not when it in originally entered. When the value of the field changes, the formula checks the prior value and the Flow triggers only if the prior value is not blank.

ISCHANGED({!$Record.fHCM2__Last_Working_Date__c})
&&
TEXT(PRIORVALUE({!$Record.fHCM2__Last_Working_Date__c}))<>""

Compensation and benefits

Indicate if pension is included in Payflow

This example is for a text field which displays Yes or No depending on whether the formula evaluates to true or not. It operates on a benefit record type of UK - Pension. If the selected picklist value for the Plan field is not Auto Enrolment - opt out and not Auto Enrolment - not eligible, the formula returns Yes.

IF ( 
    RecordType.Name = "UK - Pension" && ADP_Start_Flag__c 
    && NOT(ISPICKVAL( fHCM2__Plan__c , "Auto Enrolment - opt out"))
    && NOT(ISPICKVAL( fHCM2__Plan__c , "Auto Enrolment - not eligible")) 
    , "YES" 
    , "NO" 
    )

Include team members in compensation planning based on performance rating

The following 2 examples are for formula fields in the Employment Record to return True or False to indicate if the team member is eligible for compensation planning based on their performance rating.

Both formula examples return True when: 

• the team member's employment basis (using the value of the fHCM2__Basic__c field) is Regular

• the team member hire date is equal to or later than what is specified in the formula

• the team member's performance rating is one of the values specified in the formula

For any other scenario the formula returns False.

The first example uses the ISPICKVAL function, the second uses a custom field for the rating and compares the value of the field to what is specified in the formula.

IF( 
    ( 
    ISPICKVAL(fHCM2__Basis__c , "Regular") 
    && 
    (fHCM2__Team_Member__r.Hire_Date_Original__c <= DATE(2016,12,31)) 
    && 
    ( 
    ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "O - Outstanding") 
    || 
    ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "V - Very Good") 
    || 
    ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "G - Good") 
    || 
    ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "I - Improvement Needed") 
    ) ) 
    ,"True", "False"
  )

IF( 
    ( 
    ISPICKVAL( fHCM2__Basis__c , "Regular") 
    && 
    (fHCM2__Team_Member__r.Hire_Date_Original__c <= DATE(2016,12,31)) 
    &&
    ( 
    Current_Performance_Rating__c = "O - Outstanding" || 
    Current_Performance_Rating__c = "V - Very Good" || 
    Current_Performance_Rating__c = "G - Good" || 
    Current_Performance_Rating__c = "I - Improvement Needed"
    ) ) , 
    "True", 
    "False")

Display suggested percentages based on performance review rating

The following 2 examples are for a custom formula field to display suggested percentage ranges based on performance review ratings for the purpose of merit planning. The field is for display purposes only, not part of a recommendation formula.

The first example uses the ISPICKVAL function and the managed package Rating field in the Performance Review object. The second example uses the CASE function and an additional custom field for the rating.

To use this formula, review the picklist values used for the performance review rating and adjust accordingly.

IF( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c,
    "O - Outstanding"), "3.0% - 4.0%", 
    IF( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "V - Very Good"), "2.0% - 3.0%", 
    IF( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "G - Good"), "1.5% - 2.5%", 
    IF( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Current_Performance_Review__r.fHCM2__Rating__c, 
    "I - Improvement Needed"), "1.0% - 2.0%", 
    "0%" 
    ) 
    ) 
    ) 
    )

CASE(Current_Performance_Rating__c, 
    "O - Outstanding", "3.0% - 4.0%", 
    "V - Very Good", "2.0% - 3.0%", 
    "G - Good", "1.5% - 2.5%", 
    "I - Improvement Needed","1.0% - 2.0%" 
    , 
    "0%")

Set the percentage at 0 for some groups and a fixed percentage otherwise

This example is for a formula for salary increases or bonus amounts. The formula evaluates the values of a number of fields and sets the percentage as 0 if the team member is on an performance improvement plan or long term leave, if they have an employment end date, or if their continuous service date or current salary effective date is more recent than the date in the formula. In other cases, the formula sets the percentage as 3. The fields used are a mix of additional custom fields and managed package fields.

IF( Now_on_Performance_Improvement_Plan__c = TRUE,0, 
    IF( Active_on_Long_Term_Leave__c = TRUE,0, 
        IF(NOT(ISBLANK( fHCM2__End_Date__c )),0, 
        IF( fHCM2__Continuous_Service_Date__c >= DATE(2018,01,01),0, 
        IF( Current_Salary_Effective_Date__c >=DATE(2018,01,01),0, 
        Salary_Package_Amount__c *0.03)))))

Display reason for proposed 0 increase

This formula is for a text field to display the reason for proposing a 0 salary increase, using the

IF( Now_on_Performance_Improvement_Plan__c = TRUE,
    "Now involved in HR Event/Action", 
    IF( Active_on_Long_Term_Leave__c = TRUE,
    "On long term leave", 
    IF(NOT(ISBLANK( fHCM2__End_Date__c )),
    "Leaving the business", 
    IF( fHCM2__Continuous_Service_Date__c >= DATE(2018,01,01),
    "Started within 6 months prior to review date", 
    IF( Current_Salary_Effective_Date__c >=DATE(2018,01,01),
    "Have received a salary increase within 6 months prior to review date", 
    "N/A" 
    )))))

Salary pot formula

This example is for a currency formula field to display the salary pot for team members that rolls up to managers includes all active employees even if they are not individually eligible for an increase due to hire date, last salary increase or performance rating.

The formula uses a custom field for the salary increase percentage for active employees.

CASE ( fHCM2__Team_Member__r.fHCM2__Employment_Status__c , 
        "Active Employee", fHCM2__Annual_Salary__c 
        * Salary_Increase_Recommendation__c , 
        "On Leave",  fHCM2__Annual_Salary__c  * 0.01 , 
        0)

Salary increase percentage formula

This example is for an individual salary increase recommendation formula. It uses a number of custom fields.

The formula starts by checking if an increase is permitted. If an increase is permitted because of no increase in the current year, then the recommended increase percentage calculated with multipliers in custom fields based on the team member's hire date and their location.

CASE( Salary_increase_based_on_Recent_Increase__c ,
    "Increase Permitted",
    1,
    "No Increase Permitted",
    0,0) 
    * Salary_increase_multiplier_on_Hire_Date__c 
    * Salary_increase_based_on_country__c

Determine if increase is permitted

This example is for a formula field you can use as the Salary_increase_based_on_Recent_Increase__c in the preceding Salary increase percentage formula example.

This example checks if the year in the start date of the team member's current Salary record is same as the year in today's date, no increase is permitted and the formula returns text to indicate that. If the years do not match, an increase is permitted and the formula returns text to indicate that.

IF (YEAR (fHCM2__Current_Salary__r.fHCM2__Start_Date__c )
    =YEAR(TODAY()),
    "No Increase Permitted",
    "Increase Permitted")

Display text description of increase based on performance review rating

This example of a formula for a formula field considers the team member's performance rating and returns a text description of a recommended salary increase based on the performance rating. If there is no performance rating, then the formula returns text to indicate that.

CASE( fHCM2__Performance_Rating__c , 
        "Below expectations", "Below Recommendation", 
        "Meets expectations", "Follow Recommendation", 
        "Exceeds expectations", "Higher than Recommendation", 
        "No Performance Rating Available"
        )

Salary increase percentage

This example for a formula field uses the team member's hire date to determine the increase percentage. It If the team member was hired in the current year, the increase is 0. Team members hired last year receive an increase proportional to the time employed, based on 1/12th for each month employed last year. Team members hired before last year get the full increase.

IF (YEAR (fHCM2__Team_Member__r.fHCM2__Hire_Date__c )
    =YEAR(TODAY()),0, 
    IF (YEAR (fHCM2__Team_Member__r.fHCM2__Hire_Date__c) 
    =YEAR(TODAY())-1,
    ((13-MONTH (fHCM2__Team_Member__r.fHCM2__Hire_Date__c)) / 12)
    ,1))

Salary projection formula for eligible employees

This example is for a formula field to hold a salary projection.

This example suggests an increase of 3% for all eligible employees.

fHCM2__Current_Salary__r.fHCM2__Amount__c*1.03

Salary projection formula based on country

This example for a salary projection formula field suggests an increase based on the team member's country.

CASE( fHCM2__Team_Member__r.fHCM2__Country__c,
        "South Africa", 1.05,
        "Spain", 1.03,
        "United Kingdom", 1.02,1.01) 
        * fHCM2__Current_Salary__r.fHCM2__Amount__c

Bonus projection formula

This example for a bonus projection formula suggests a bonus of 2% of current salary for all employees.

fHCM2__Current_Salary__r.fHCM2__Amount__c*0.02

Bonus projection based on performance rating

This example for a bonus projection formula suggests a bonus percentage depending on the team member's performance rating.

CASE(fHCM2__Performance_Rating__c,
    “Exceeds Expectations”, 0.05,
    “Meets Expectations”, 0.03,
    “Below Expectations”, 0.01,0.00
    ) 
    * fHCM2__Current_Salary__r.fHCM2__Amount__c

Eligibility formula

This example is for a compensation planning eligibility formula. When it returns True, the team member is eligible. The formula returns true when all the following are true:

• the team member has an employment status of Active Employee

• the team member's Continuous Service Date is the date given in the formula or earlier

• the team member's performance rating is not of Development Need

(ISPICKVAL( fHCM2__Team_Member__r.fHCM2__Employment_Status__c,
    "Active Employee") &&
    fHCM2__Continuous_Service_Date__c < DATE(2019,12,31) &&
    fHCM2__Performance_Rating__c <> "Development Need",
    True,
    False)

Exclusion formula

This example is for a compensation planning exclusion formula. When it returns True, the team member is excluded.

The formula returns True when one of the following is true:

• the employment basis is not Permanent: Full Time or Permanent: Part Time

• the team member's Performance Review Date is prior to the date given in the formula

IF(
    ((text(fHCM2__Basis__c) <> "Permanent: Full Time" )
    && (text(fHCM2__Basis__c) <> "Permanent: Part Time"))
    || fHCM2__Performance_Review_Date__c < DATE(2020,06,01),
    True,False
    )

Legacy salary planning formula for new annual salary

This formula for a number field is for the legacy salary planning process. It considers eligible team members based on a custom eligibility field, and returns their country multiplier. It then uses the country multiplier to generate a new annual salary. The Annual Salary/Annual Multiplier should be the same value at the Amount field on Salary. If the Salary Eligibility field is not checked for a team member, the formula returns 0.

IF( Salary_Eligibility__c = "True",
    (CASE( fHCM2__Team_Member__r.fHCM2__Country__c ,
     "UK",0.10,
     "US",0.06,
     "France", 0.05,
      0.04)
      * fHCM2__Annual_Salary__c  /  fHCM2__Current_Salary__r.fHCM2__Annual_Multiplier__c)
    ,0
   )

People management

Concatenate home address fields into one

This example is for a concatenated text formula field.

It concatenates all team member home address fields with a line break for each line. The formula checks if Home Address lines 2 and 3 are populated before including the values.

fHCM2__Home_Address_1__c & 
    IF(NOT(ISBLANK(fHCM2__Home_Address_2__c)), (BR() 
    & fHCM2__Home_Address_2__c),
    IF(NOT(ISBLANK(fHCM2__Home_Address_3__c)), (BR() 
    & fHCM2__Home_Address_3__c), BR())) 
    & fHCM2__Home_Address_City__c & BR() 
    & fHCM2__Home_Address_Region__c & BR() 
    & fHCM2__Home_Address_Country__c & BR() 
    & fHCM2__Home_Address_Postal_Code__c

Create a full name field

This example is for a formula field to create a full name field. The formula checks if the Preferred Name field is populated and if so returns the value, otherwise it returns the first name. The formula checks if the Middle Name field is populated and if so concatenates it, if not return nothing, then the formula concatenate with the Surname.

BLANKVALUE(fHCM2__Preferred_Name__c,fHCM2__First_Name__c)
+
IF(ISBLANK(fHCM2__Middle_Name__c),'',' '+fHCM2__Middle_Name__c)+' '+fHCM2__Surname__c

Display actual working hours on the Employment record

This example is for a formula field to display the team member's actual working hours as a number.

The formula checks if the Hours Worked field on the Employment Record has a value and returns if it has, then checks if the team member has a Policy Override, and if yes, returns the Hours Per Week from the policy. If the team member has no override, the formula returns the Hours Per Week from the policy assigned to the team member's HR Department.

IF( 
    ISBLANK( fHCM2__Hours_Worked__c ), 
    IF( 
        ISBLANK( fHCM2__Team_Member__r.fHCM2__Policy__c ), 
        fHCM2__Team_Member__r.fHCM2__Department__r.fHCM2__Policy__r.fHCM2__Hours_Per_Week__c, 
        fHCM2__Team_Member__r.fHCM2__Policy__r.fHCM2__Hours_Per_Week__c ), 
        fHCM2__Hours_Worked__c )

Display length of service as years and months

This example is for a formula field to display the length of service as years and months rather than a decimal number in years, such as 5.57 years). The displayed value is xx years xx months.

IF( ISBLANK(fHCM2__Current_Employment__r.fHCM2__End_Date__c), 
TEXT(FLOOR(( TODAY() 
- fHCM2__Current_Employment__r.fHCM2__Continuous_Service_Date__c) / 365.2425))
& " year(s) " & TEXT(FLOOR(MOD(( TODAY() 
- fHCM2__Current_Employment__r.fHCM2__Continuous_Service_Date__c),
365.2425) / 30)) & " month(s)", 
TEXT(FLOOR(( fHCM2__Current_Employment__r.fHCM2__End_Date__c 
- fHCM2__Current_Employment__r.fHCM2__Continuous_Service_Date__c) 
/ 365.2425)) & " year(s) " 
& TEXT(FLOOR(MOD(( fHCM2__Current_Employment__r.fHCM2__End_Date__c 
- fHCM2__Current_Employment__r.fHCM2__Continuous_Service_Date__c),
365.2425) / 30)) & " month(s)" 
)

Calculate anniversary date

This example is for a formula field to calculate a team member's work anniversary date. You can use the resulting field in a summary process displaying anniversaries.

DATE( YEAR(TODAY()) + IF (MONTH(TODAY()) > 
MONTH( fHCM2__Current_Employment__r.fHCM2__Start_Date__c ) 
|| (MONTH(TODAY()) 
== MONTH(fHCM2__Current_Employment__r.fHCM2__Start_Date__c) 
&& DAY(TODAY()) > DAY(fHCM2__Current_Employment__r.fHCM2__Start_Date__c)),1,0),  
MONTH(fHCM2__Current_Employment__r.fHCM2__Start_Date__c) , 
IF ( MONTH(fHCM2__Current_Employment__r.fHCM2__Start_Date__c) == 2 
&& DAY(fHCM2__Current_Employment__r.fHCM2__Start_Date__c) == 29, 28, 
DAY(fHCM2__Current_Employment__r.fHCM2__Start_Date__c))) 

Display a textual description of the amount of vacation in weeks on the Employment Record

This example is for a formula field to display the amount of vacation based on the length of service measured in service months. The values of vacation length are defined in the formula.

IF(fHCM2__Service_Months__c < 60, "4 Weeks", 
    IF(fHCM2__Service_Months__c >= 60 
        && fHCM2__Service_Months__c < 72, "5 Weeks", 
    IF(fHCM2__Service_Months__c >= 72 
        && fHCM2__Service_Months__c < 84, "6 Weeks", 
    IF(fHCM2__Service_Months__c >= 84 
        && fHCM2__Service_Months__c < 96, "7 Weeks", 
    IF(fHCM2__Service_Months__c >= 96 
        && fHCM2__Service_Months__c < 108, "8 Weeks", 
    IF(fHCM2__Service_Months__c >= 108 
        && fHCM2__Service_Months__c < 120, "9 Weeks", 
    IF(fHCM2__Service_Months__c >= 120 
        && fHCM2__Service_Months__c < 132, "10 Weeks", 
    IF(fHCM2__Service_Months__c >= 132 
        && fHCM2__Service_Months__c < 144, "11 Weeks", 
    "12 Weeks" 
    ))))))))

Checkbox to display if the team member left last month

This example is for a formula field to display a checkbox field to indicate if the team member left last month using the employment End Date. The formula excludes team members whose employment status is Non Starter.

OR( 
    YEAR( fHCM2__End_Date__c ) = YEAR(TODAY()) 
    && MONTH(fHCM2__End_Date__c ) = MONTH(TODAY()) - 1 
    && NOT( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Employment_Status__c , 
    "Non Starter") ) , 
    YEAR(fHCM2__End_Date__c ) = YEAR(TODAY()) 
    - 1 && MONTH(fHCM2__End_Date__c ) = 12 
    && MONTH(TODAY()) = 1 
    && NOT( ISPICKVAL(fHCM2__Team_Member__r.fHCM2__Employment_Status__c , 
    "Non Starter") ) 
    )

Display a date a specific number of days away from a specified date field

This example is for a formula field to display the date of a day that is a specified number of working days away from the value of a date field using the formula. This example displays the date that is 75 working days from the employment Start Date.

CASE(
    MOD(fHCM2__Start_Date__c - DATE(1900, 1, 7), 7),
    0, (fHCM2__Start_Date__c) + 75 + FLOOR((75-1)/5)*2,
    1, (fHCM2__Start_Date__c) + 75 + FLOOR((75)/5)*2,
    2, (fHCM2__Start_Date__c) + 75 + FLOOR((75+1)/5)*2,
    3, (fHCM2__Start_Date__c) + 75 + FLOOR((75+2)/5)*2,
    4, (fHCM2__Start_Date__c) + 75 + FLOOR((75+3)/5)*2,
    5, (fHCM2__Start_Date__c) + 75 + CEILING((75)/5)*2,
    6, (fHCM2__Start_Date__c) - IF(75>0,1,0) + 75 + CEILING((75)/5)*2,
    null
    )

Display bonus information on the Employment Record

This example is for a UDF_on_Target_Variable formula field to displays a bonus amount based on the On Track Bonus field or a calculation using a custom Bonus field.

MAX( fHCM2__On_Track_Bonus__c , fHCM2__Annual_Salary__c * Bonus__c ) 

Display total compensation on the Employment Record

This example is for a formula field to display a team member's total compensation as a sum of annual salary, on-track commission and the on target bonus (using in the formula in another example, see Display bonus information on the Employment Record).

fHCM2__Annual_Salary__c  +  UDF_On_Target_Variable__c +  
fHCM2__On_Track_Commission__c 

Probation period formula

This example is for a formula to use in a workflow rule to set a team member's probation period end date. The workflow rule is typically included in Sage People implementations.

The formula uses a custom Probation Period picklist with the following values:

• 1 Week

• 1 Month

• 6 Weeks

• 3 Months

• 6 Months

• 1 Year

• No Probation Period

You can use other options in the picklist, and amend the probation periods in the formula to suit the needs of the organization.

To ensure the workflow does not set a probation end date for those team members who are not required to have a probation period, set Probation period not equal to 'No Probation Period' as a criteria in the workflow rule.

To ensure team members have the probation period entered, make sure you add the Probation Period field to the New Hire Field Set on Employment and make it mandatory.

CASE( Probation_Period__c , 
        "1 Week",  fHCM2__Start_Date__c  + 6, 
        "1 Month",  ADDMONTHS(fHCM2__Start_Date__c,1)-1,
        "6 Weeks",  fHCM2__Start_Date__c  + 41,
        "3 Months",  ADDMONTHS(fHCM2__Start_Date__c,3)-1,
        "6 Months",  ADDMONTHS(fHCM2__Start_Date__c,6)-1,
        "1 Year",  ADDMONTHS(fHCM2__Start_Date__c,12)-1,
        null
    )

No onboarding email for certain employment statuses

This example for a formula to use in the Conditional Upon field in an action event. It stops the onboarding email from being sent to new starters when their contract type is set to Non Employee – Agency Worker or Non Employee – Contractor using a custom field Contract Type.

NOT(Contract_Type__c = 'Non Employee - Agency Worker') 
&& 
NOT(Contract_Type__c = 'Non Employee - Contractor')

Display zip code based on Remote work location

This example for a formula field takes the zip code of the team member's home address if the work location type called Remote is selected and the value of another custom field is true. The work location type is determined using a custom picklist field and its display is also determined by a checkbox field Display Home Address in GAL being true.

IF(ISPICKVAL(
    fHCM2__Current_Employment__r.fHCM2__Work_Location__r.Work_Location_Type__c,"Remote")
    && Display_Home_Address_in_GAL__c =TRUE,  
    fHCM2__Current_Employment__r.fHCM2__Work_Location__r.fHCM2__Address_Postal_Code__c ,  
    fHCM2__Home_Address_Postal_Code__c )

Display a combined entity and employment basis field in the Team Member record

This example is for a formula field on the Team Member record to display the values of a custom field called Entity and employment basis of the current employment record.

TEXT(fHCM2__Current_Employment__r.UDF_Entity__c ) 
    + "-" 
    + TEXT(fHCM2__Current_Employment__r.fHCM2__Basis__c)

Turnover report

This example for a formula field uses the field combining entity and employment basis, Entity_Emp_Type__c (see Display a combined entity and employment basis field in the Team Member record), to return specified values used by Turnover Report.

CASE( Entity_Emp_Type__c , 
    "New York Studio-Permanent (E)","NY Permanent (E)", 
    "New York Studio-Sub Contractor/Recruitment Agency (N)", "NY SC / RA (N)", 
    "New York Studio-Freelance/Independent Contractor (N)"," NY FL/IC (N)", 
    "Excluded")

Display a team member's salary band range

This example is for a formula field on the employment record to display the top and bottom of the salary band where the team member's salary falls.

The example adds the currency symbol £ to the range values. If you use multiple currencies for salaries, you can also get the currency code from the salary band with TEXT(fHCM2__Salary_Band__r.fHCM2__Currency__r.fHCM2__Currency_Code__c).

"£ " & 
TEXT(fHCM2__Salary_Band__r.fHCM2__Bottom__c) & 
" - " & 
"£ " & 
TEXT(fHCM2__Salary_Band__r.fHCM2__Top__c)

Display country flag icons on a team member record

This example is for a formula field to display the flag of the team member's country on the team member record. Before adding the formula field, add the images you need to Files or Static Resources in the org and copy the image addresses for the formula.

For other examples of the IMAGE function, see Images and Display an icon on a record page.

IF(fHCM2__Country__c="UK",
    IMAGE("/resource/1566243416000/Country_Icon_UK","United Kingdom",30,45),
IF(fHCM2__Country__c="USA",
    IMAGE("/resource/1566243308000/Country_Icon_USA","United States",30,45),
IF(fHCM2__Country__c="India",
    IMAGE("/resource/1566243206000/Country_Icon_India","India",30,45),
IF(fHCM2__Country__c="China",
    IMAGE("/resource/1566243475000/Country_Icon_China","China",30,45),
IMAGE("DEFAULT IMAGE URL","Unknown",30,45)))))

Performance and talent management

Text based instructions for performance reviews

This example is for a formula field which enables you to add a block of text instructions to a performance review form. The formula concatenates bits of text and displays them on different lines.

"Contributions "& BR() 
    & " What were the three contributions you made this year that delivered the highest impact?"
    & BR() 
    & "If associated with a deal, please include:" 
    & BR() 
    & "1. Deal origination/key relationship management" & BR()
    & "2. Decision made on the deal" & BR()
    & "3. The overall result or outcome"

Talent acquisition

Translations for picklist values in email templates with a text field

This example is for a formula field to translate picklist values in an email template. Picklist values in emails are included on the basis of the locale and language settings of the user who is triggering the email.

If a user whose language is set to English sends an email where the template is in French and includes picklist values as merge fields, the picklist values are included in English even if the values have French translations. A formula field provides aworkaround for this issue.

This example translates the picklist values for Employment Type in Recruit from English to French. You need to create the formula field on the object where the picklist field you want to translate is hosted, and then insert the formula field as a merge field into the email template.

CASE( fRecruit__Employment_Type__c ,
        "Agency Worker", "Travailleur intérimaire",
        "Apprenticeship", "Apprentissage",
        "Contractor", "Entrepreneur",
        "Intern", "Stagiaire",
        "Temporary", "Temporaire",
        "Trainee/Graduate", "Stagiaire/diplômé",
        "Permanent", "Permanent",
        null)

Transform a multi-select picklist for WX

This example is for a formula field to transform a multi-select picklist, which are not supported in WX, into a text field for use in WX. The formula uses a custom Location Multi field.

IF(INCLUDES( Location_Multi__c , "Location 1"), "Location 1" + BR() , NULL) 
    + IF(INCLUDES( Location_Multi__c , "Location 2"), "Location 2" + BR() , NULL) 
    + IF(INCLUDES( Location_Multi__c , "Location 3"), "Location 3" + BR() , NULL)

Time to fill a vacancy

This example is for a formula field to calculate and display the time to fill a vacancy as the time elapsed after the Approved Date or as the time between Approved Date and the date when the vacancy was filled. The UDF_Filled_Date__c in the formula example is a custom date field.

IF (ISBLANK (UDF_Filled_Date__c) = TRUE, 
    TODAY() - fRecruit__Approved_Date__c, 
    UDF_Filled_Date__c - fRecruit__Approved_Date__c)

Time and Attendance

Rounding half up

A formula field to replicate absence accrual rule rounding mode Half Up for numbers, which rounds to nearest 0.5. The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

IF(fta__Year_End_Balance__c = FLOOR( fta__Year_End_Balance__c  ) + 0, 
FLOOR(fta__Year_End_Balance__c ) + 0, 
IF(fta__Year_End_Balance__c < FLOOR(fta__Year_End_Balance__c ) + 0.5, 
FLOOR(fta__Year_End_Balance__c ) + 0.5, 
IF(fta__Year_End_Balance__c > FLOOR(fta__Year_End_Balance__c ) + 0.5, 
FLOOR(fta__Year_End_Balance__c ) + 1, 
(fta__Year_End_Balance__c )))) 

Rounding half up to 1

A formula field to replicate absence accrual rule rounding mode Half Up for numbers, which rounds to nearest 1.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

IF(fta__Year_End_Balance__c = FLOOR(fta__Year_End_Balance__c ) + 0,
    FLOOR(fta__Year_End_Balance__c ) + 0,
 
    IF(fta__Year_End_Balance__c < FLOOR(fta__Year_End_Balance__c ) + 0.5,
        FLOOR(fta__Year_End_Balance__c ) + 1,
 
    IF(fta__Year_End_Balance__c > FLOOR(fta__Year_End_Balance__c ) + 0.5,
        FLOOR(fta__Year_End_Balance__c ) + 1,
 
        (fta__Year_End_Balance__c ))))

Rounding half down

A formula field to replicate absence accrual rule rounding mode Half Down for numbers, which rounds to nearest 0.5.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

IF(fta__Year_End_Balance__c = FLOOR(fta__Year_End_Balance__c ) + 0,
    FLOOR(fta__Year_End_Balance__c ) + 0,
 
    IF(fta__Year_End_Balance__c < FLOOR(fta__Year_End_Balance__c ) + 0.5,
    FLOOR(fta__Year_End_Balance__c ) + 0,
 
    IF(fta__Year_End_Balance__c > FLOOR(fHCM2__Year_End_Balance__c ) + 0.5,
    FLOOR(fta__Year_End_Balance__c ) + 0.5,
 
    (fta__Year_End_Balance__c ))))

Rounding half down to the nearest 1

A formula field to replicate absence accrual rule rounding mode Half Down for numbers, which rounds to nearest 1.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

IF(fta__Year_End_Balance__c = FLOOR(fta__Year_End_Balance__c ) + 0,
    FLOOR(fta__Year_End_Balance__c ) + 0,
 
    IF(fta__Year_End_Balance__c < FLOOR(fta__Year_End_Balance__c ) 
    + FLOOR(fta__Year_End_Balance__c ),
    FLOOR(fta__Year_End_Balance__c ),
 
    IF(fta__Year_End_Balance__c > FLOOR(fta__Year_End_Balance__c ) 
    + FLOOR(fta__Year_End_Balance__c ),
    FLOOR(fta__Year_End_Balance__c ),
 
    (fta__Year_End_Balance__c ))))

Rounding to ceiling, half up

A formula field to replicate absence accrual rule rounding mode Ceiling for numbers, which rounds to nearest 0.5.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

IF(fta__Year_End_Balance__c - FLOOR(fta__Year_End_Balance__c ) = 0 
|| fta__Year_End_Balance__c - FLOOR(fta__Year_End_Balance__c ) = 0.5,
    fta__Year_End_Balance__c ,
    IF(fta__Year_End_Balance__c - FLOOR(fta__Year_End_Balance__c ) < 0.5,
    fta__Year_End_Balance__c 
    + (0.5 -( fta__Year_End_Balance__c - FLOOR(fta__Year_End_Balance__c ))),
   fta__Year_End_Balance__c 
    + ( CEILING(fta__Year_End_Balance__c ) - fta__Year_End_Balance__c )))

Rounding to ceiling

A formula field to replicate absence accrual rule rounding mode Ceiling for numbers, which rounds to nearest 1.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

CEILING(fta__Year_End_Balance__c)

Rounding to floor

A formula field to replicate absence accrual rule rounding mode Floor for numbers, which rounds to nearest 1.

The example uses the Year End Balance field in the Accrual Balance object, but the formula can be used for any other number fields you need to reflect rounding on, by changing the field API name.

For example, this formula can be used for Annual Allowance Pro Rata. In particular it can be used to display rounded Vacation balances in WX in the Team/Vacation and Absence/Vacation Balances report.

FLOOR(fta__Year_End_Balance__c)

Timesheet submission formula

This example is for a formula field in the Timesheet object to determine if the timesheet is for the current week. You can then use the field in a validation rule, see Permit timesheet submission only for current week.

IF((fHCM2__Start_Date__c-TODAY()) 
+ 
MOD(TODAY() - fta__Timesheet_Pattern__r.fta__Base_Date__c, 7) >= 0 
 && (fHCM2__Start_Date__c-TODAY()) 
 + 
MOD( TODAY()- fta__Timesheet_Pattern__r.fta__Base_Date__c, 7) 
<= 6 ,true,false)