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
Thank you very much for this. Very useful to know this is a possibility.
I can get it to work up to the point of the full string with years and days, then I get an Invalid Expression error. I have tried moving around the quotes, using double quotes to no avail.
If change the ‘ Years’ for 123 it comes up, so not sure whats going on? Any ideas?
Use a text formula
Perfect thank you!
