Skip to main content

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.

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

         ))

      )

   )

)

Reply


Terms & Conditions