Skip to main content

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

  • March 3, 2024
  • 3 replies
  • 122 views
P
Forum|alt.badge.img+11

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

Dan_Montoya
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • March 3, 2024

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.

TheTimeSavingCo
P
Forum|alt.badge.img+11
  • Project_NP_LS
  • Author
  • Known Participant
  • March 3, 2024

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.


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
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • March 3, 2024

using your entire formula this data looks correct

 

Terms & ConditionsAccessibility statement