RE: Custom field calculation
I am currently putting together a test system for our HR team. I have added a few field calculations into some of the fields. One example is for length of service:
(({today}-{hiredate})/365.25)
This displays the length of service in years. Is it possible to get it to display 1 year and 1 month for example? It seems to only give full years. I can’t figure out the syntax for this.
Thanks,
{today}-{hiredate} returns the difference between the 2 dates in days. Dividing that number by 365.25 converts it to the difference in years with a fraction component in the decimals. Remove the fractional component using TRUNC to get the first number of your desired output. TRUNC(({today}-{hiredate})/365.25)
Divide the difference in days by 30.44 to get the difference in months. Same as the year, you want this without the fractional part, though you may want to round using something other than TRUNC. You would want the remainder of that after diving by 12 to get your months which is done via MOD. MOD(TRUNC(({today}-{hiredate})/30.44), 12)
Combine them using the string concatenation operator (||) to get what you want: TRUNC(({today}-{hiredate})/365.25) || ' Years ' || MOD(TRUNC(({today}-{hiredate})/30.44), 12) || ' Months'
You may get strange results if your hire date is in the future.
Perfectly valid answer. Also wanted to add the use of the function “MONTHS_BETWEEN” instead of dividing:
FLOOR(MONTHS_BETWEEN({today}, {hiredate})/12)||' years '||FLOOR(MOD(MONTHS_BETWEEN({today}, {hiredate}),12))||' months'
As battk said, you get negative values if the hiredate is in the future
