Calculating/adding anniversaries with date functions

Dan Tanner
Dan Tanner ✭✭✭✭
in Formulas and Functions

Good morning, hoping someone can help out with this.

I'm trying to create a formula using hire date and today's date to calculate the number of years working for a company. For example, if someone was hired October 12, 2019, I would like to have a "years with company" automate to "2" and send a notification to my e-mail. Is there any possible way to do this?

Thanks,

Dan

· Share on FacebookShare on Twitter

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Dan Tanner 

    Hope you are fine, you can add 2 columns :

    1- Date column type call it [hire date].

    2- Text / Number column type call it [number of working years]

    then use the following formula and convert it to column format formula to calculate how many year each employee complete:

    =ROUNDDOWN((TODAY() - [hire date]@row) / 365, 0)

    The following screenshot shows a sample


    image.png


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Dan Tanner 

    Your welcome and i will be happy to help you any time,  I will be grateful for your "Vote Up" or "Insightful"

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!