Help

Calculate difference between 2 dates and return answer in Years and Days

Topic Labels: Formulas
258 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Project_NP_LS
6 - Interface Innovator
6 - Interface Innovator

Hello AT Community,

I am trying to write a formula that returns the number of years and days in between two dates. So for example, assuming {DoB} is 18 FEB 1994, DATETIME_DIFF(TODAY(),DoB,'years'),"y ") returns 30 which is correct.

I would also like to calculate the number of remaining days i.e. between the last full year (18 FEB 2024 and today). The answer should be 14 however this extract from my Airtable formula (MOD(DATETIME_DIFF(TODAY(),DoB,'days'),365),"d"))) is returning 21.

Can anyone tell me what I am doing wrong please?

3 Replies 3
Dan_Montoya
Community Manager
Community Manager

Looks like you are not accounting for leaps years in your formula.  The total days in 30 years is not 30*365, it is 30*365+(30/4), which adds 7 additional days.

Project_NP_LS
6 - Interface Innovator
6 - Interface Innovator

Hi Dan, 

Thanks for your reply. In the meantime, this is the formula that I am using 

IF(DoB,
CONCATENATE(
DATETIME_DIFF(TODAY(), DoB, 'years'),"y ",
 
DATETIME_DIFF(
TODAY(), DATEADD(DoB, DATETIME_DIFF(TODAY(), DoB, 'years'), 'years'), 'days'),"d"))
 
STEP 1
DATETIME_DIFF(TODAY(), DoB, 'years'): Calculates the total number of years between the current date (TODAY()) and the

STEP 2
Date of Birth (DoB).
DATEADD(DoB, DATETIME_DIFF(TODAY(), DoB, 'years'), 'years'): Calculates the date corresponding to the next birthday after the most recent completed year. This is achieved by adding the number of years calculated in step 1 to the Date of Birth.

STEP 3
DATETIME_DIFF(TODAY(), ..., 'days'): Calculates the total number of days between the current date and the next birthday after the most recent completed year (as calculated in step 2).

STEP 4
CONCATENATE(...): Combines the calculated years and days into the desired format ("y d").
Dan_Montoya
Community Manager
Community Manager

using your entire formula this data looks correct

 

Screenshot 2024-03-03 at 2.23.49 PM.png