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).
Reminder: why there is no way to do day/month/year for a single column in a worksheet when the language selected by the planner is US English. (credit Phil MacGovern).
Date fields are shown in the format specified by the language. If planners want to see the dates as day/month/year they need to switch to a different language pack than US (settings > language in top right corner), like UK English (but that effects all dates for all employees including employees in the US).
The toDate function cannot help us here. Even though it can help convert a string column to a Date column, the second parameter (MM/dd/yyyy or dd/MM/yyyy) is a descriptor of what format the string is in, not the expected outcome. If source field is a string, then with the toDate function we are saying “I have a date string in this format: dd/MM/yyyy; convert it to a Date type. So if we have 3/26/2022 in the source field it takes the 3 as the day and 26 as the month which leads to an error and a blank in the target column.
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.
—
All the best,
Xavier
(If you found this blog useful please consider giving it a Like)
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.
Hi
Thanks for this blogs. this is very use full
Is any formula for Hire date - current system date (by default) convert in total month.
example
Hire Date 1/1/2020
Current System Date 29/08/2023
so value should show 43 month
Regards
Ravindra
Hi Ravindra Kumar Sharma
You can divide the number of days (explained in the blog) by 30 and it will give you an estimate of the number of months.
There is no way I know of to make it always 100% exact however. If you want that you would need to build it through a custom MDF object where there is a business rule that allows for 100% months calculation based on the number of days of each month within the period.
All the best
Xavier
Good day,
May I please ask a question on numbers formatting, slightly but not directly related to the above?
Hello,
I have asked this question on formatting numbers in ‘Set Numbers Format Rules’ in the Compensation tool in the Learning room Compensation forum in the past and I got the explanation provided in the Implementation Guide as an answer. Unfortunately, this didn’t help much, as my problem is precisely that; that I don’t understand the explanation provided in the Implementation Guide. I also got referred to this article, which does not talk about these format rules.
They have then advised by one of the advisers at the Learning room forum I post my questions to this existing blog and the Implementation SMEs could take a look
So, I would appreciate it if you could advise on my two questions below:
1) To format numbers rounded to the left of the decimal point, type in "#,###" in the "Format" field.
2) To display a comma as a thousands separator, include a comma in the number format
3) To format numbers with decimal point, include the following digit placeholders in a section.
MY QUESTION: what happens if a number has less digits to the right of the decimal point than there are placeholders in the format?
MY QUESTION: what happens if a number has the same digits to the right of the decimal point as there are placeholders in the format?
MY QUESTION: what happens if there are less digits to the left of the decimal point than there are placeholders?
MY QUESTION: what happens if there the same digits to the left of the decimal point than as are placeholders?
# (number sign) displays only significant digits and does not display insignificant zeros.
0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.
2. QUESTION NUMBER TWO: PLEASE KINDLY VERIFY if the codes I have given to the following examples (in red) are correct. I would like to test if I am beginning to understand the rules.
And if they are not correct, please explain why and provide the correct ones. I am trying to find the consistent logic to all examples, and until now, I am failing to find it.
Very many thanks for your continued support
Hello, your table is correct; it looks like you understand how the formatting works.
When creating a format, I think of it like this. A '#' symbol displays the digit if it exists. A '0' displays a digit regardless. If one doesn't exist, it adds a 0.
If the number is 3.47 and the format is:
I hope this helps.
Excellent, thanks very much for verifying and for providing a clear, straight forward explanation