Return Multiple Unit Specifiers for DATETIME_DIFF

Question

Sorry if this is a basic question— I am just starting out learning how to use formulas with little/no prior excel experience.

I have a formula that calculates the difference between today's date and the date a video was released. I have it returning the # of days:

DATETIME_DIFF({Today}, {Video Date},'days')

Is it possible that instead of returning the number of days, it could return # of years, #of months, #of days? So, instead of 952 days ago, it returns: 2 years 7 months 10 days?

Thanks for any help.

AirOps · Accepted Answer

Hi @bdahl!

Welcome to the world of Airtable! We are happy to have you here. Please don't apologoize for asking any kind of question, we are here to help .

To answer your question, yes formatting the output to appear as you requested is possible, but does require what could be a considered confusing formula (especially for a beginner). I will input my solution below, and the explain what each piece of the formula is doing.

DATETIME_DIFF(TODAY(), {Video Date}, "y") & " years "

& 

DATETIME_DIFF(DATEADD(TODAY(),-DATETIME_DIFF(TODAY(), {Video Date}, "y"),"y"), {Video Date}, "M") & " months " 

& 

 DATETIME_DIFF(DATEADD(TODAY(),-DATETIME_DIFF(TODAY(), {Video Date}, "M"),"M"), {Video Date}, "d") & " days "

Breaking it down:

DATETIME_DIFF(TODAY(), {Video Date}, "y") & " years "

&

DATETIME_DIFF(DATEADD(TODAY(),-DATETIME_DIFF(TODAY(), {Video Date}, "y"),"y"), {Video Date}, "M") & " months "

&

DATETIME_DIFF(DATEADD(TODAY(),-DATETIME_DIFF(TODAY(), {Video Date}, "M"),"M"), {Video Date}, "d") & " days "

Finds the difference in years and then adds the text "years" to the output of the formula

Subtracts the difference in years between the two dates, and finds the remaining difference in months. Then adds the text "months" to the output of the formula

Subtracts the difference in months between the two dates, and finds the remaining difference in days. Then adds the text "days" to the output of the formula.

I hope this helps!

Tip: If you don't want the formula to produce "#ERROR" when a Video Date is blank, surround the formula with an IF statement to check if the date exists, and only execute the formula if it returns true:

IF({Video Date}, ...insert formula here... )

Wow— thank you so much! Yeah, I would have struggled with writing that on my own; it worked exactly as I wanted it. I REALLY appreciate you taking the time to explain the formula to me— it makes much more sense now. Thank you again!

bdahl · Answer