Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Display date duration with a decimal format rather than whole numbers

Topic Labels: Formulas
Solved
Jump to Solution
121 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

Hi @Allan_Mayes, try this out:

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

Screenshot 2022-09-13 at 2.04.34 PM

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

See Solution in Thread

2 Replies 2

Hi @Allan_Mayes, try this out:

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

Screenshot 2022-09-13 at 2.04.34 PM

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

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