Technical Articles
Dates and Roundings in Compensation
Overview
Formulas using dates can be difficult to implement in SuccessFactors Compensation templates due to the complexity of the formula parameter.
Additionally some Employee Central fields such as Age (but also Compa-Ratio or Range Penetration and a significant number of other fields) cannot be retrieved directly from EC into a column of a compensation worksheet because they are Transient fields, which means they are calculated on the fly in EC and their data isn’t stored.
Finally, roundings in standard recommendation columns or in custom formulas are also complicated to set up either because they require XML edits or because the formulas are not obvious to configure (nested conditions).
CALCULATING A NUMBER OF DAYS BETWEEN DATES
In this 3 minutes recording we go through the steps required to build a formula that recalculates the age of an employee in a compensation worksheet using the dateDiff function. Please find more Q&A on dateDiff formulas on the SAP blog question thread here.
Formula : round(‘down’,(dateDiff(toDate(customDateOfBirth,”MM/dd/yyyy”),toDate(“09/29/2020″,”MM/dd/yyyy”))/365))
Note : if we need to recalculate the seniority of an employee (Time in Position is a transient field in EC and its data cannot be retrieved in comp planning worksheets) please know that we cannot bring in Today’s date dynamically. We would have to either hardcode a date in the formula as per my example above on calculating the Age (our date would need to be aligned with the Comp Cycle start date) or get the date from a lookup table (preferred solution which is easier to maintain for customers).
Recalculating proration in a custom field with dateDiff formula :
How to use dateDiff to recalculate employee proration in a custom field based on the Hire Date in EC mapped to the standard column “startDate” in the compensation template (this is useful if we would like to display the proration % for each employee but not have it impact standard money columns as per the standard proration feature) :
Formula : if((dateDiff(toDate(“01/01/2021″,”MM/dd/yyyy”),startDate)/365) <= 0,1,(dateDiff(toDate(“01/01/2021″,”MM/dd/yyyy”),startDate)/365))
USING A DATE FROM A LOOKUP TABLE FOR PUBLISH BACK TO EC OR MDF
Formula : toDate(lookup(‘2021_Publish_back_settings’,EE_Country,payroll_area_code,2),’MM/dd/yyyy’)
BUILDING IF-THEN RULES WITH DATES
If we want to build an if-then rule where the output will always be a date (for example to handle exceptions for the effective date of the publish back to EC), then we can build our rule the following way (all input fields and the one output field are column type = Date)
Example 1 :
Example 2 : same than example 1 but with 2 override dates (for example one for merit and one for lumpsum)
If we want to build complex if-then rules using dates and decide whether our output is a date or any other text then we must turn all input columns as well as the output column into String type. Please see examples below.
Example 3 :
Example 4 :
Example 5 :
Example 6 :
Example 7 : If there is no date, then…
ADDING ROUNDINGS IN COMPENSATION WORKSHEETS
In both cases below (standard or custom fields), SuccessFactors always applies roundings on the local currency value of the recommendation. Which means that in the case of larger currencies (Yen, Rupies…) a lot of inaccuracy can be introduced when toggling back and forth between functional and local currency currency (the percentage may keep increasing because the system will first convert the local currency into functional currency, then round).
We recommend whenever possible to round final payout values instead of recommendation columns. And to remember to always test rounding configuration changes in Local Currency view which is the only view valid for testing due to the above explanations.
- Rounding in standard recommendation fields
To add rounding on all standard recommendation fields such as merit, lumpsum, extra or promotion, we need to download the template xml and add the following line to the MONEY format :
To round 1001.2 to 1100, use <comp-number-format-ext-multiple roundingMode=”up”>100</comp-number-format-ext-multiple>
To round 1001.2 to 1010, use <comp-number-format-ext-multiple roundingMode=”up”>10</comp-number-format-ext-multiple>
To round 1001.2 to 1002, use <comp-number-format-ext-multiple roundingMode=”up”>1</comp-number-format-ext-multiple>
The xml tag “roundingMode” can have the values “up” or “down” or “halfUp” (halfUp is the default prebuilt roundingMode for each format when the tag isn’t defined).
Please note the following :
- this tag ONLY applies to the following fields : merit / promo / extra / extra2 / lumpsum / lumpsum2 / stockunit / stock / option / stockother1 / stockother2 / stockother3 / and custom editable fields of type Money on the worksheet.
- this tag doesn’t round the Current Salary and Final Salary fields or any other standard read-only field of type Money. To round these fields we will need to replace them with a custom field (see below) and adjust the configuration of the publish back to Employee Central.
- Specific Money formats can also be configured based on the paycomponent frequency of an employee (ANNUAL, MONTHLY, ANNUAL) or the currency code.
The recording below shows examples of roundings in standard recommendation fields :
- Rounding on custom fields (credit Phil MacGovern – SAP)
To round the outcome of a calculated field we need to create a new custom column of type Money and pick one of the formula below :
Rounding to nearest 5 cents (for example always round to .00, .25 or .75 / see business case here) : round(‘halfUp’,lumpSum*20)/20
Rouding to nearest 5: round(‘halfUp’,round(‘halfUp’,lumpSum)/5)*5
Rounding to nearest 10: round(‘halfUp’,round(‘halfUp’,lumpSum)/10)*10
Rounding to nearest 100: round(‘halfUp’,round(‘halfUp’,lumpSum)/100)*100
Rounding Up always: round(‘up’,round(‘up’,lumpSum))
Rounding Down always: round(‘down’,round(‘down’,lumpSum))
- Rounding on the last decimal in custom fields (credit Phil MacGovern – SAP)
Please note : we can only control rounding within the Number Format already defined and there is no number format for STRING so the formulas below are only relevant for Amount, Money and Percentage type of columns.
Rounding down on the last decimal defined in the column Format (for example #,##0.00 or #,##0.##) : round(“Down”,finSalary*100/unitPerYear)/100
Rounding halfUp on the last decimal defined in the column Format (for example #,##0.00 or #,##0.##) : round(“halfUp”,finSalary*100/unitPerYear)/100
Rounding up on the last decimal while keeping the number of decimals defined in the column Format (for example #,##0.00 or #,##0.##) : round(“Up”,finSalary*100/unitsPerYear)/100
Business case : make sure when converting from annual to monthly that the employee doesn’t lose out. Let’s take an example : If we divide a salary of 70,000 by 12 we get 5,833.33333 which rounds to 5,833.33. But if we multiply that 5,833.33 by 12 then in Employee Central Pay Component Group we get 69999.96 which is lower than what was on the compensation worksheet. By doing round(“up”,(finSalary/12)*100)/100 then we get 70,000.08 in the Pay Component Group in EC and hence make sure the employee doesn’t lose out.
Thank you !
Hello Xavier, Thanks for this blog. In most of the implementations we come across this requirement to create a column for the date difference.
Hello Xavier,
Thanks for this blog, i am calculating tenure in years formula for my client ,can you help me out with a solution with correction on the below formula using date function. Cust_LastPromotion & Cust_DOJ are columns ids.
round("down",if(cust_LastPromotion="NA",(dateDiff(toDate(cust_DOJ,"dd/mm/yyyy"),toDate("06/06/2021","dd/mm/yyyy"))/365),
(dateDiff(toDate(cust_LastPromotion,"dd/mm/yyyy"),toDate("06/06/2021","dd/mm/yyyy"))/365)))
Hi Kishore Gunashegaran
I wouldn't be able to help just by looking at it, working with formulas with dates takes a lot of time and testing.
Here is my tip : try to break down the different parts of your formula into different columns of your worksheet so you can easily spot which nested condition is the problematic one, fix it, then merge it back together in one formula.
Thanks !
HI Xavier,
I was referring to DOB calculation done in the initial video. would like to know if you store current date somewhere.
dateDiff(toDate(Customdob,”MM/dd/yyyy”),toDate(“12/14/2021″,”MM/dd/yyyy”))/365
Pl let me know . Thanks for your help
Thanks
Ritanshi
Hi Xavier,
Good day!
Would like to check if we can have a formula for Date like for example. HireDate is 05-05-2022 then add +365 days so on the worksheet it will show as 05-04-2023. Will this be possible?
Regards,
Hi,
As far as I know it would only be possible with a lookup table.
All the best,
Xavier
Hi Xavier
I am in a situation where i need to round only the decimals not the number in compensation template
Example
Value to be converted from 19.724101 to 19.73 (always round up) as per requirement from customer
Currently when i round up 19.724101 it gives me 20
Any help will be appreciated...is this possible?
Thanks
Kavita
Hi Kavita Jain
it's in the blog higher...

Xavier
Thanks that worked. Appreciate your support.