Calculating the Year, Month, and Days for Tenure

Reva Cenevil

Hello,

I am attempting to calculate tenure in years, months, and days given the total # of days. I calculated the # of days between hire date and term date in 1 column (# of Days), and used the formula below in another column. It is not calculating correctly, and I am getting decimal numbers for the months and days integers. What do I need to change in the formula and how do I get it to round so there are no decimals behind the ones place?

=INT([# of Days]@row / 365) + " " + "Years" + " " + ROUND([# of Days]@row - (INT([# of Days]@row / 365) * 365), 1) / 12 + " " + "Months" + " " + ROUND([# of Days]@row - INT([# of Days]@row / 365) * 365) / 7 + " " + "Days"

The answer I am getting is: 1 Year 5.75 Months 9.85714 Days broken down for 434 days.

Thank you!

Paul Newcome

I think maybe we can adjust the parenthesis for the ROUND functions, and you should be up and running. Try this one...

=INT([# of Days]@row / 365) + " " + "Years" + " " + ROUND([# of Days]@row - (INT([# of Days]@row / 365) * 365) / 12) + " " + "Months" + " " + ROUND(([# of Days]@row - INT([# of Days]@row / 365) * 365) / 7) + " " + "Days"

Reva Cenevil

Unfortunately, that did not work. It gave me a error message of #unparseable. I have been trying to see if I can do it another way. I have created additional helper columns. I now have one to calculate the years, one to calculate month, and one to calculate the days. The years and days formulas are working just fine, but I have still having trouble with the months.

For instance, for 1698 days. I used the following formula for years, =INT([# of Days]@row / 365), and got 4 years.

For the months, I used =IF([# of Days]@row < 365, ROUND(INT([# of Days]@row / 30), 0), ROUND(INT([# of Days]@row - 365) / 30, 0)), and got 44 months

*How do I substract the number of years that have already been calculated?

For the days, I used =IF(DAY([Term Date]@row) - DAY([Hire Date]@row) < 0, 30 + DAY([Term Date]@row) - DAY([Hire Date]@row), DAY([Term Date]@row) - DAY([Hire Date]@row)) and got 25 days

Paul Newcome

=INT([# of Days]@row / 365) + " Years " + INT(([# of Days]@row - (Years@row * 365)) / 30) + " Months " + [# of Days]@row - ((INT([# of Days]@row / 365) * 365) + ((INT(([# of Days]@row - (INT([# of Days]@row / 365) * 365)) / 30)) * 30)) + " Days"

Give this one a try. I was finally able to get into a sheet to do some testing, and this one seems to be working for me.

Leonora Meng

FYI

=INT([# of Days]@row / 365) + " Years " + INT(([# of Days]@row - INT([# of Days]@row / 365) * 365) / 30) + " Months " + ([# of Days]@row - INT([# of Days]@row / 365) * 365 - INT(([# of Days]@row - INT([# of Days]@row / 365) * 365) / 30) * 30) + " Days"

This one worked out for me.