Help

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

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
357 2
cancel
Showing results for 
Search instead for 
Did you mean: 
mikeprescottace
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hmm, here's what I ended up with.  Not 100% sure it's what you're looking for though
Screenshot 2024-02-07 at 5.16.58 PM.png

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

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hmm, here's what I ended up with.  Not 100% sure it's what you're looking for though
Screenshot 2024-02-07 at 5.16.58 PM.png

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!