Calculating Tenure in years

[former_member32554]

We are calculating tenure in years formula for my client ,can anyone 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)))

[xavierlegarrec]

Hi Kishore,

If you are having issues with this formula you should break it down in different columns : create one column with formula = dateDiff(toDate(cust_DOJ,"dd/mm/yyyy"), another one with toDate("06/06/2021","dd/mm/yyyy"), etc... so you find out easily where the issue comes from.

I created some documentation on formulas with dates here : https://blogs.sap.com/2020/09/29/how-to-use-dates-in-formulas-in-compensation-worksheets/

All the best,

Xavier

[Former Member · Accepted solution]

I don't know exactly what your error is, but I know you are using the 'toDate' funciton slightly incorrectly. It should be:

toDate("01/01/2023","dd/MM/yyyy")

Note the "MM" versus your "mm".