Help

Calculate # of years of service based on next anniversary date

Topic Labels: Formulas
827 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jennifer_Poiry
4 - Data Explorer
4 - Data Explorer

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
         ))
      )
   )
)