Help

Re: Display date duration with a decimal format rather than whole numbers

Solved
Jump to Solution
962 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Allan_Mayes
4 - Data Explorer
4 - Data Explorer

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
TheTimeSavingCo
17 - Neptune
17 - Neptune

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
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

Allan_Mayes
4 - Data Explorer
4 - Data Explorer

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