Skip to main content

Calculate # of years of service based on next anniversary date

  • June 16, 2021
  • 1 reply
  • 15 views
J

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

Kamille_Parks11
Forum|alt.badge.img+27

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
         ))
      )
   )
)
Terms & ConditionsAccessibility statement