Help

Stumped: How to return an age (date difference) in months plus days

Topic Labels: Formulas
Solved
Jump to Solution
1018 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Joy_Morin
4 - Data Explorer
4 - Data Explorer

I am trying to create a formula to return the difference in time between two dates in terms of the number of months and number of days. For example, being age “8 months 3 days.”

This is the formula I came up with so far, but this direction doesn’t get me an accurate return since not every month has exactly 30 days in it:

(DATETIME_DIFF({DATE},‘2 Feb 2020’,‘M’)) & " mos " & ((DATETIME_DIFF({DATE},‘2 Feb 2020’, ‘d’))-(DATETIME_DIFF({DATE},‘2 Feb 2020’, ‘M’))*30) & " days "

Any ideas? Maybe I need to start over using different functions?

1 Solution

Accepted Solutions
Joy_Morin
4 - Data Explorer
4 - Data Explorer

Got it! I’m posting my solution here, in case anyone else is interested. This formula will accurately return an age as “X months, X days.”

(DATETIME_DIFF({DATE}, ‘2 FEB 2020’, ‘months’) ) & ’ mos, ’ & (DATETIME_DIFF((DATEADD({DATE}, -(DATETIME_DIFF({DATE}, ‘2 FEB 2020’, ‘months’) ), ‘month’)), ‘2 Feb 2020’, ‘days’)) & ’ days’

Notes:

  • {DATE} refers to a column containing the date of the event.
  • Replace ‘2 FEB 2020’ with the birthday.

See Solution in Thread

1 Reply 1
Joy_Morin
4 - Data Explorer
4 - Data Explorer

Got it! I’m posting my solution here, in case anyone else is interested. This formula will accurately return an age as “X months, X days.”

(DATETIME_DIFF({DATE}, ‘2 FEB 2020’, ‘months’) ) & ’ mos, ’ & (DATETIME_DIFF((DATEADD({DATE}, -(DATETIME_DIFF({DATE}, ‘2 FEB 2020’, ‘months’) ), ‘month’)), ‘2 Feb 2020’, ‘days’)) & ’ days’

Notes:

  • {DATE} refers to a column containing the date of the event.
  • Replace ‘2 FEB 2020’ with the birthday.