Birthday/Anniversary Formula & Reminders

Jennica · 04/08/22

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 · 04/08/22

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 · 04/08/22

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 · 04/08/22

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

Smartsheet User 99 · 04/08/22

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 · 04/08/22

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 · 04/08/22

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 · 06/10/22

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 · 09/28/22

@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å · 09/28/22

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 · 09/28/22

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å · 09/28/22

@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 · 09/28/22

@Andrée Starå

Amazing, thank you so much! Working perfectly :)

Have a lovely weekend Andree!

Best wishes,

Chloe

Andrée Starå · 09/28/22

@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 · 11/30/22

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

Birthday/Anniversary Formula & Reminders

Best Answer

Answers

Categories