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
-
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
-
=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
-
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. :(
-
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
-
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!!!
-
@Francesca Radabaugh Happy to help! I LOVE @row. I always get frustrated with Excel for not having that as an option!
-
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 :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!