Calculating years of service

Hi there!

I have a sheet that we use to track key dates for staff (anniversary date, years of service, etc. etc.) I have a formula that seems to be successfully calculating the years of service, but when I try to set this as a column formula I am getting an error message (and the help article really isn't helping). :(

Here is a copy of the formula:

=IFERROR(IF(DATE(YEAR(TODAY()), MONTH([Start Date]4), DAY([Start Date]4)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]4), YEAR(TODAY()) - YEAR([Start Date]4) - 1), "Need Start Date")

The error message I am getting states:

The column formula syntax isn't quite right, see our help article.....

Thank you in advance anyone who can help!

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Francesca Radabaugh

    Try this:

    =IFERROR(IF(DATE(YEAR(TODAY()), MONTH([Start Date]@row), DAY([Start Date]@row)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]@row), YEAR(TODAY()) - YEAR([Start Date]@row) - 1), "Need Start Date")

    Because you had a 4 after every cell reference, it has a hard time converting that to a column formula. If you use @row instead, it will work.


    Best,

    Heather

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!