Skip to Content
Technical Articles
Author's profile photo Xavier Le Garrec

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 :

  1. 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.
  2. 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.
  3. 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)

Assigned Tags

      14 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Radhika Goyal
      Radhika Goyal

      Hello Xavier, Thanks for this blog. In most of the implementations we come across this requirement to create a column for the date difference.

       

      Author's profile photo Kishore Gunashegaran
      Kishore Gunashegaran

      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)))

       

       

      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      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 !

      Author's profile photo Ritanshi Gupta
      Ritanshi Gupta

      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

       

      Author's profile photo Rocer Cerveza
      Rocer Cerveza

      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,

      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      Hi,

      As far as I know it would only be possible with a lookup table.

      All the best,

      Xavier

      Author's profile photo Kavita Jain
      Kavita Jain

      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

       

      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      Hi Kavita Jain

      it's in the blog higher...

      Xavier

       

       

      Author's profile photo Kavita Jain
      Kavita Jain

      Thanks that worked. Appreciate your support.

      Author's profile photo Ravindra Kumar Sharma
      Ravindra Kumar Sharma

      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

      Author's profile photo Xavier Le Garrec
      Xavier Le Garrec
      Blog Post Author

      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

      Author's profile photo Susana Moran
      Susana Moran

      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. QUESTION NUMBER 1: MY QUESTIONS IN ITALICS ON THE RULES:

      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.

      • If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders.

      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?

      • If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed.

       

      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?

       

      • If the format contains only number signs (#) to the left of the decimal point, numbers less than one begin with a decimal point.

      # (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.

      NUMBER FORMAT CODE
      1.23 1.230 #.##0
      1.23 1.23 #.##
      1.23 1.2 #.#
      1 1.00 #.00
      1 01.00 0#.00
      12 12.0 #.0

      Very many thanks for your continued support

      Author's profile photo Philip MacGovern
      Philip MacGovern

      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:

      #.## 3.47 There is a number for each placeholder
      #.### 3.47 There is no digit for the last #, so it doesn't display
      #.# 3.5 It displays two digits as the format describes and rounds the result
      #.##00 3.4700 No digits exist beyond the 7, so the system adds zeroes
      #,###.00 3.47 No digits exist before the 3, so they aren't displayed. There are two numbers after the decimal so they are displayed. If there weren't, zeroes would be displayed instead.

      I hope this helps.

      Author's profile photo Susana Moran
      Susana Moran

      Excellent, thanks very much for verifying and for providing a clear, straight forward explanation