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 are also a complicated topic when building custom formulas.
How to calculate a number of days between two 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.
Note : if you 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 you cannot bring in today’s date dynamically. You would have to either hardcode a date in the formula as per my example above on calculating the Age (your 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).
How to build if-then rules using dates :
If you 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 you can build your rule the following way (all input fields and the one output field are column type = Date)
If you want to build complex if-then rules using dates and decide whether your output is a date or any other text then you must turn all input columns as well as the output column into String type. Please see examples below.
Example 1 :
Example 2 :
Example 3 :
Example 4 :
How to round to the nearest number in a custom formula (example with the standard column for lumpsum below) (credit Phil MacGovern – SAP)
You need to create a new custom column of type Money and pick one of the formula below :
Rounding 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
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.