Display date duration with a decimal format rather than whole numbers

[Allan_Mayes]

I’m hoping someone can help me with a simple formula. I currently have a formula that counts the months between a start date and today’s date and displays it in months as whole numbers (ie, 2 months, 5 months, etc).

The issue with just a whole number for months is that at 3 months and 29 days, it will show “3 months”, and at 3 months and 1 day, it will also show as “3 months”. A more accurate figure would be helpful. I also need the figure displayed in months, so the display in just weeks (ie, 9 weeks) or just days (ie, 52 days) wouldn’t be helpful in this case.

I’m hoping there is a way to make it display in months with one decimal place (ie, 2.3 months, 5.7 months). I’d also settle for something similar such as “2 months 14 days” or even “3 months / 2 weeks”.

I’ve tried using the formatting function within the settings and set it to “decimal” with a precision of “1.0” but it still just shows it as the whole number for the month with a “.0” at the end. For example, 3 months and 2 weeks would still show as 3.0 months instead of 3.5 months.

The formula I am currenting using is:

DATETIME_DIFF(TODAY(),{Start Date}, ‘months’)

If anyone out there can offer any assistance, that would be much appreciated.

Thanks in advanced!

[TheTimeSavingCo · Accepted solution]

Hi @Allan_Mayes, try this out:

ROUND(
  DATETIME_DIFF({End}, {Start}, 'days') / 30,
  1
) & " months"

It assumes every month is 30 days, but eh, we’re rounding to one decimal point anyway so I figured it’s fine

[Allan_Mayes]

Thanks @Adam_TheTimeSavingCo it works great! Such a simple idea that I had completely overlooked.