Skip to content
  • Try for free

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Calculating/adding anniversaries with date functions

    ✭✭✭✭✭
    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

    Sign in
    Try for free

    Best Answers

    • ✭✭✭✭✭✭
      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
    • ✭✭✭✭✭✭
      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

    • ✭✭✭✭✭✭
      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
    • ✭✭✭✭✭

      Amazing. Thank you kindly.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      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

    Sign in
    Try for free

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions