formula answer from decimal to year, month

Lisbeth Fuentes-Fernandez

Hi I need to a formula to convert an answer that i get from a formula to a year, month format

Formula I am using =(TODAY() - DATEONLY([Appointment Start Date]2)) / 365 = 2.27397

Convert the 2.27397 to Year, Month

Paul Newcome

@Lisbeth Fuentes-Fernandez I responded on your other post before I saw this one. How exactly would you want it displayed?

Lisbeth Fuentes-Fernandez

Hi @Paul Newcome

Im hoping to have the days display as years and months eg 1yr 2mo or 1,2, really anything where the year and month will display rather than days or a decimal.

But i need to keep the formula that i am already using on the cell

Thank you!

Paul Newcome

There is no way to change the format, but we can modify the formula.

=INT((TODAY() - DATEONLY([Appointment Start Date]@row)) / 365) + "yr " + ROUND(((TODAY() - DATEONLY([Appointment Start Date]@row)) / 365 - INT((TODAY() - DATEONLY([Appointment Start Date]@row)) / 365)) * 12) + "mo"

Lisbeth Fuentes-Fernandez

Thank you!!!! It works!!!!

Paul Newcome

Great! Happy to help! 👍️

Lisbeth Fuentes-Fernandez

One more, lol

How can I apply that formula to the column??

Paul Newcome

You mean to every row? You should be able to dragfill by clicking on the cell with the formula. Hover over the bottom right corner, and your cursor should change into a +. Click and drag to highlight the rest of the column, and it should pull the formula.

Another option would be to click on the cell containing the formula then SHIFT+Click on the last cell in the column to highlight the whole thing and then use CTRL+D.