Seniority Formula

[Armenito (Armen)]

Hi everyone!
I need help with a formula column.

So I have three columns… START, END, and SENIORITY.
The START and END columns are both dates.
The SENIORITY column is a formula where I’d like to indicate in year and month how long an employee has been with us wether he is still an active employees or a past employee.

So here’s the formula I came up with but it doesnt seem to work:

IF(MONTH({End}) > 0,CONCATENATE((ROUNDDOWN((DAYS({End},{Start})/30.4)/12,0))," years"," “, (ROUNDDOWN((((DAYS({End},{Start})/30.4)/12)-(ROUNDDOWN((DAYS({End},{Start})/30.4)/12,0)))*12,0)),” months"),CONCATENATE((ROUNDDOWN((DAYS(Today(),{Start})/30.4)/12,0))," years"," “, (ROUNDDOWN((((DAYS(Today(),{Start})/30.4)/12)-(ROUNDDOWN((DAYS(Today(),{Start})/30.4)/12,0)))*12,0)),” months")

So essentially, I would like the SENIORITY column to use the END date to make its calculations if there is in fact a date in the END column however if the END column is blank, I would like the calculation to be made based on today’s date.

Both CONCATENATE works well on their own however, once I add the IF function, it no longer works.

Any ideas?

Thank you!!

[JCorrell (Jim - The Monday Man)]

@Armenito

I didn’t check/change the math but otherwise this should work for you:

CONCATENATE(
(ROUNDDOWN((DAYS(IF({End}<>"", {End}, TODAY()), {Start})/30.4)/12,0)), " years ",
(ROUNDDOWN((((DAYS(IF({End}<>"", {End}, TODAY()), {Start})/30.4)/12)-(ROUNDDOWN((DAYS(IF({End}<>"", {End}, TODAY()), {Start})/30.4)/12,0)))*12,0)),
" months")

Jim - Subscribe to The Monday Man
Watch Our Latest Video: POWERFUL Item/Subitem Templates with Integromat - YouTube