Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Calculate # of years of service based on next anniversary date

Topic Labels: Formulas
418 1
cancel
Showing results for 
Search instead for 
Did you mean: 

How can I calculate the # of years of service someone will have worked on their (future) anniversary date? The best I can come up with is # years they’ve worked as of today, but I want to list upcoming anniversaries and their years of service on those dates.

1 Reply 1

Instead of doing DATETIME_DIFF(TODAY(), {First Date}, 'years'), you would do DATETIME_DIFF(TODAY(), {Next Date}, 'years')

where {Next Date} is a formula that looks like this:

IF(
   {First Date}, 
   DATETIME_PARSE(
      DATETIME_FORMAT(
         {First Date}, 
         'MM/DD/' & (YEAR(TODAY()) + IF(
            DATETIME_PARSE(DATETIME_FORMAT({First Date}, 'MM/DD/' & YEAR(TODAY()))) < TODAY(),
            1, 
            0
         ))
      )
   )
)