Convert Numbers (Days) to To Year/Month

Karrie Davis

I'm trying to convert number of days to year and month. In excel I would use the following formula

=DATEDIF(0,[Days closed]11,"y") &" years, "&DATEDIF(0,[Days closed]11,"ym") &" months"

 

Though this formula does not work in smartsheet is there another way to do this

 

Paul Newcome

Exactly how are you wanting the data to be displayed?

Karrie Davis

I would like it to look something like 3year and 2 month

Paul Newcome

Try something along the lines of

 

=INT(Days@row / 365) + " years" + ROUND((Days@row - (INT(Days@row / 365) * 365)) / 12) + " months"

@LS

@Paul Newcome When I try the formula you note above, it is not calculating correctly for me. For example I have "120" which is selected in the "days@row" which on my sheet is "duration@row" as you have above, but the result is 0 years 10 months.

Sonia.DomGon

Hi @LS

Did you figure out how to do it? Thank you!

Ian D

The following modified formula works

=INT(Days@row / 365) + " years " + ROUND((Days@row - (INT(Days@row / 365) * 365)) / (365 / 12)) + " months"