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.
Page 1 / 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
))
)
)
)
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.