Formulas | Formula library

Sage People implementers and others working in customer projects have contributed the formulas in this library over time. New contributions are welcome and you can help us by:

• Letting us know if there’s a problem with any formula in the library
• Continue to contribute formulas you create that others can use
• Providing feedback

We intend the formulas in the formula library to work as examples and starting points for you to build on. Many formulas use other 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. For example, 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 two 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

You can use this example for a formula field to format a number you can use in the PDF template. For example, the Amount in the Salary record. 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 two formula fields

This example shows how to split a picklist value containing text and numbers into two separate fields. One contains the text element and the other contains the number element. Typically, you'll use such text and number combinations in department names or cost centers. For example, you have a picklist field for cost centers. 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 eight characters. They assume you separate the number and text value by a consistent number of characters (three, 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 the values of multiple fields into a text field

You can use this example for a formula field to roll up the values of multiple fields into a single field. You can use the formula field 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 display either N or Y. This depends on if the fields the system details in the formula have a value or not. The fields we've used in the example are a mix of managed package fields and additional custom fields.

You can use this kind of formula to ensure that the required fields complete 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 a value you enter changes

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

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

Compensation and benefits

Indicate if Payflow includes pension

This example is for a text field that 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 picklist value you select for the Plan field isn't Auto Enrollment - opt out or Auto Enrollment - 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 two examples are for formula fields in the Employment Record to return True or False. This is 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 you specify in the formula

• the team member's performance rating is one of the values you specify 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 you specify 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 two examples are for a custom formula field to display suggested percentage ranges based on performance review ratings. This is for 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 you use 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 zero for some, a fixed percentage otherwise

This example is for a formula for salary increases or bonus amounts. The formula evaluates the values of various fields and sets the percentage as zero. This is if the team member is on a performance improvement plan or long-term leave. It's also if they have an employment end date, or if their continuous service date is more recent than the date in the formula. In other cases, the formula sets the percentage as three. The fields you use 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.

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. This rolls up to managers and includes all active employees, even if they aren't individually eligible for an increase.

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 various custom fields.

The formula starts by checking if you permit an increase. If you do permit an increase due to no increase in the current year, you proceed with the calculation. You calculate the increase percentage you recommend using multipliers in custom fields based on the team member’s hire date and 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 the increase is permitted

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

This example checks if the start date year of the team member's current Salary record is the same as the year today. No increase is permitted and the formula returns text to indicate that. If the years don't 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. It returns a text description of a recommended salary increase based on the performance rating. If there's 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. If you hired the team member in the current year, the increase is zero. Team members you hired last year receive an increase proportional to the time you employ them. This is based on 1/12th for each month employed last year. Team members you 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. It depends 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 in the formula or earlier

• the team member's performance rating isn't 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, it excludes the team member.

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

• the employment basis isn't Permanent: Full Time or Permanent: Part-Time

• the team member's Performance Review Date is before the date 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. It returns their country multiplier. It then uses the country multiplier to generate a new annual salary. The Annual Salary/Annual Multiplier is the same value at the Amount field on Salary. If you don't check the Salary Eligibility field for a team member, the formula returns zero.

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 you've populated the Home Address lines two and three 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 you've populated the Preferred Name field and if so returns the value. Otherwise it returns the first name. The formula checks if you've populated the Middle Name field and if so concatenates it. If not, it returns nothing, and concatenates 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 it checks if the team member has a Policy Override. If yes, it returns the Hours Per Week from the policy. If the team member has no override, the formula returns the Hours Per Week from the HR Department's policy.

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. For example, 5.57 years. The system displays the value as 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 the 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

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

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. It indicates 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

This example is for a formula field to display the date of a day. It's a number of working days you specify away from the value of a date field using the formula. This example displays the date that's 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 display a bonus amount. It's 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. It includes 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. Implementations typically includetThe workflow rule.

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 your organization.

Ensure the workflow doesn't set a probation end date for those team members who don't need 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 in place, add the Probation Period field to the New Hire Field Set on Employment. 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 sending to new starters when their contract type is Non Employee, Agency Worker, or Contractor. It uses 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 you select the work location Remote. It also needs the value of another custom field is true. The system determines the work location type using a custom picklist field. It also determines its display 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

This example is for a formula field on the Team Member record. It's 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 to return values you specify. You'll use them in the Turnover Report. (See Display a combined entity and employment basis field ).

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. This is 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. 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 that lets you 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. The system includes picklist values in emails based on the locale and language settings of the user who's triggering the email.

If a user whose language is English sends a French email template including picklist values as merge fields, the system includes values in English. this is even if the values have French translations. A formula field provides a workaround 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 sits. 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 into a text field for 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 the Approved Date and the date when you filled the vacancy. 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 the nearest 0.5. The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 1.

The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 0.5.

The example uses the Year End Balance field in the Accrual Balance object. however, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 1.

The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 0.5.

The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 1.

The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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 the nearest 1.

The example uses the Year End Balance field in the Accrual Balance object. However, you can use the formula for any other number fields you need to reflect rounding on. Just change the field API name.

For example, you can use this formula for Annual Allowance Pro Rata. In particular you can use it 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)