Custom field calculation

Answered

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,

Rookie Asked on August 6, 2020 in Other.
Add Comment
2 Answer(s)
Best answer

{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.

Intermediate Answered on August 6, 2020.

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

on August 6, 2020.
Add Comment

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?

Rookie Answered on August 6, 2020.

Use a text formula

on August 6, 2020.

Perfect thank you!

on August 6, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor