Create a "next work anniversary" date from employee start date.

Question

I want to automate an email that goes to staff two weeks before their leave year renews on the anniversary of their start date each year.

Is it possible to create a formula which will automatically show the next anniversary in a date format?

For example:

If my start date is 28/02/2019 and today is 07/02/2024 then the next anniversary date should read 28/02/2024

Or if my start date is 28/02/2019 and today is 01/03/2024 then the next anniversary date would read 28/02/2025

I'm UK based so using DD-MM-YYYY formatting.

I'm struggling to generate anything that works - any help appreciated.

TheTimeSavingCo · Accepted Answer

Hmm, here's what I ended up with. Not 100% sure it's what you're looking for though

And here's the formula:

IF(
  IS_BEFORE(
    TODAY(),
    DATETIME_PARSE(
      DATETIME_FORMAT(
        {Start Date},
        "DD MMM"
      ) & YEAR(TODAY()),
      "DD MMM YYYY"
    )
  ),
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {Start Date},
      "DD MMM"
    ) & YEAR(TODAY()),
    "DD MMM YYYY"
  ),
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {Start Date},
      "DD MMM"
    ) & 
    YEAR(
      DATEADD(
        TODAY(),
        1,
        'year'
      )
    ),
    "DD MMM YYYY"
  )
)

It basically takes the day and month and converts it to this year, then it checks whether today is before that date. If it is, it'll display that day and month with this year. If today's after that date, then it'll display the day and month with next year instead

That worked like a dream, you're a genius!

I've been round and round with so many different possible solutions but this worked first time.

Much appreciated!

mikeprescottace · Answer

Hmm, here's what I ended up with. Not 100% sure it's what you're looking for though

And here's the formula:

IF(
  IS_BEFORE(
    TODAY(),
    DATETIME_PARSE(
      DATETIME_FORMAT(
        {Start Date},
        "DD MMM"
      ) & YEAR(TODAY()),
      "DD MMM YYYY"
    )
  ),
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {Start Date},
      "DD MMM"
    ) & YEAR(TODAY()),
    "DD MMM YYYY"
  ),
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {Start Date},
      "DD MMM"
    ) & 
    YEAR(
      DATEADD(
        TODAY(),
        1,
        'year'
      )
    ),
    "DD MMM YYYY"
  )
)

It basically takes the day and month and converts it to this year, then it checks whether today is before that date. If it is, it'll display that day and month with this year. If today's after that date, then it'll display the day and month with next year instead

That worked like a dream, you're a genius!

I've been round and round with so many different possible solutions but this worked first time.

Much appreciated!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded