Calculating years of service

Francesca Radabaugh · 05/07/21

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!

Heather Duff · 05/10/21

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

James Keuning · 05/08/21

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

try that out

Francesca Radabaugh · 05/10/21

It is now saying #incorrect argument set. :( The weird thing is my original formula does compute the right answer, its just when I try to set it as a column formula that I get an error. :(

Heather Duff · 05/10/21

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

Francesca Radabaugh · 05/10/21

Thank you so much Heather! That did the trick!

I will keep in mind the @row formula reference, that makes total sense. Thank you again!!!

Heather Duff · 05/10/21

@Francesca Radabaugh Happy to help! I LOVE @row. I always get frustrated with Excel for not having that as an option!

Princess M. · 01/29/23

https://community.smartsheet.com/discussion/comment/290645#Comment_290645

This formula "worked" but I need it to put the actual calculation in the space and not the words in " ". I'm not a formula guru, so I know this question probably seems silly, but any help would be awesome :)

Calculating years of service

Best Answer

Answers

Help Article Resources

Categories