Birthday/Anniversary Formula & Reminders

Options
Jennica
Jennica
edited 11/30/22 in Smartsheet Basics

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

  • Smartsheet User 99
    Smartsheet User 99 ✭✭✭
    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

  • Smartsheet User 99
    Smartsheet User 99 ✭✭✭
    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?

  • Jennica
    Options

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

  • Smartsheet User 99
    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.

  • Jennica
    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.

  • Smartsheet User 99
    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)




  • JME22
    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.


  • Chloe MB
    Chloe MB ✭✭✭✭
    Options

    @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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    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.

  • Chloe MB
    Chloe MB ✭✭✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

  • Chloe MB
    Chloe MB ✭✭✭✭
    Options

    @Andrée Starå

    Amazing, thank you so much! Working perfectly :)

    Have a lovely weekend Andree!


    Best wishes,

    Chloe

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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!

    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.

  • Yanie
    Yanie ✭✭
    Options

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