Birthday/Anniversary Formula & Reminders

Jennica

I would like notifications to send for both work anniversary dates, and birthdays. Easiest way is likely creating a date column populated from a formula and automation reminders based on that column.

I currently have columns for:

1 - "Company date of hire"

2 - "Employee Birthday"

Smartsheet User 99

Here is the formula to keep the Date and Month of the Hire, but adding current year to it

=DATE(YEAR(TODAY()), MONTH([Company Date of Hire]@row), DAY([Company Date of Hire]@row))

Here is the formula to keep the Date and Month of birthday, but adding the current year to it

=DATE(YEAR(TODAY()), MONTH([Employee Birthday]@row), DAY([Employee Birthday]@row))

Are you going to want to know "x" amount of days before their anniversary or birthday to set up a party or anything like that?

Smartsheet User 99

Here is the formula to keep the Date and Month of the Hire, but adding current year to it

=DATE(YEAR(TODAY()), MONTH([Company Date of Hire]@row), DAY([Company Date of Hire]@row))

Here is the formula to keep the Date and Month of birthday, but adding the current year to it

=DATE(YEAR(TODAY()), MONTH([Employee Birthday]@row), DAY([Employee Birthday]@row))

Are you going to want to know "x" amount of days before their anniversary or birthday to set up a party or anything like that?

Jennica

:O You literally just blew my mind! Thank you so much!!

Smartsheet User 99

You're welcome, let me know if you want to calculate the number of days before their anniversary or bday, and if you need help setting up automation.

Jennica

Actually what I am trying to do now is have a column that will show how many years of service they currently have:

I've tried two that don't work:

=(YEAR(TODAY() - YEAR([Company Date of Hire]@row)))

and

=ROUNDDOWN((TODAY() - [Company Date of Hire]@row) / 365.25, 0)

The second works, but either is too early, or too late. 365.25 accounts for leap years. If I use 365 in a year or two the dates are far off.

Smartsheet User 99

I edited the formulas to add an IFERROR statement. If I didn't add the IFERROR() statement, then you'll get those errors in the cells once a new name gets put in. It won't disappear until dates are put in. I'm sure no one wants to see a sheet with #INVALID all over the place

Company Hire Anniversary

=IFERROR(DATE(YEAR(TODAY()), MONTH([Company Date of Hire]@row), DAY([Company Date of Hire]@row)), "")

Birthday

=IFERROR(DATE(YEAR(TODAY()), MONTH([Employee Birthday]@row), DAY([Employee Birthday]@row)), "")

Years @ Company (decrease decimal until there's no decimal)

=IF(ISBLANK([Company Hire Anniversary]@row), "", ([Company Hire Anniversary]@row - [Company Date of Hire]@row) / 365)

JME22

Hi, I am looking to do something similar but none of the formulas above work. I'm looking for a formula to calculate service anniversary date so that its current to the year and to count the years of service.

Chloe MB

@Smartsheet User 99

Hello!

I am wondering if you can help me. I have tried your above two formulas but they are not working. Please see the details below. Can you see where it is going wrong?

=DATE(YEAR(TODAY()), MONTH(DOB@row), DAY(DOB@row))

I look forward to hearing from you :)

Andrée Starå

Hi @Chloe MB

I hope you're well and safe!

Ensure that the Birthday column is a date column.

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chloe MB

Hey @Andrée Starå

Thank you so much for coming back to me! I am well thank you, I hope you are too.

Yes, that worked, thank you! Do you also know how I can find the employee's age?

Best wishes,

Chloe :)

Andrée Starå

@Chloe MB

Excellent!

Happy to help!

Try something like this.

=(TODAY() - DOB@row) / 365

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chloe MB

@Andrée Starå

Amazing, thank you so much! Working perfectly :)

Have a lovely weekend Andree!

Best wishes,

Chloe

Andrée Starå

@Chloe MB

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Yanie

Can someone help me i try the formula but its not working