Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Jun 16, 2021 01:26 PM
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.
Jun 16, 2021 02:10 PM
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
))
)
)
)