# Birthday/Anniversary Formula & Reminders

Options
edited 11/30/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"

## Best Answer

• Answer ✓
Options

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?

## Answers

• Answer ✓
Options

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?

• Options

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

• Options

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.

• Options

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.

• Options

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)

• Options

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.

• ✭✭✭✭
Options

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 :)

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

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 :)

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

Amazing, thank you so much! Working perfectly :)

Have a lovely weekend Andree!

Best wishes,

Chloe

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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