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