How to calculate length of service?
Good afternoon all,
Hoping to learn how to calculate length of service with the results displayed as either Years/Months/Days or Years/Months
At present, I've tried a few formulae which I've found on the community previously, however there seem to be a few issues with inaccurate results.
This formula seems to produce the closest results to what I would expect to see, however it's appears to be adding a year in which is confusing results in some cases:
=YEAR(TODAY()) - YEAR([Job start date]@row) + " years " + IF(MONTH(TODAY()) - MONTH([Job start date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Job start date]@row), MONTH(TODAY()) - MONTH([Job start date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Job start date]@row) < 0, 30 + DAY(TODAY()) - DAY([Job start date]@row), DAY(TODAY()) - DAY([Job start date]@row)) + " days"
I've tried to use the formula with a "-1" added in to remove the year, however this is then impacting data elsewhere in the report:
=YEAR(TODAY()) - YEAR([Job start date]@row) -1 + " years " + IF(MONTH(TODAY()) - MONTH([Job start date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Job start date]@row), MONTH(TODAY()) - MONTH([Job start date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Job start date]@row) < 0, 30 + DAY(TODAY()) - DAY([Job start date]@row), DAY(TODAY()) - DAY([Job start date]@row)) + " days"
I was able to create a formula which worked in Excel, however this won't translate in to SmartSheet from what I understand.
=DATEDIF(D3,TODAY(),"y") & " Years, " & DATEDIF(D3,TODAY(),"ym") & " Months, " & DATEDIF(D3,TODAY(),"md") & " Days"
Please let me know if there's any additional info required - any help is greatly appreciated!
Best Answer
-
Try this:
=YEAR(TODAY()) - YEAR([Job start date]@row) - IF(DATE(YEAR([Job Start Date]@row), MONTH(TODAY()), DAY(TODAY())) < [Job Start Date]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Job start date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Job start date]@row), MONTH(TODAY()) - MONTH([Job start date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Job start date]@row) < 0, 30 + DAY(TODAY()) - DAY([Job start date]@row), DAY(TODAY()) - DAY([Job start date]@row)) + " days"
Answers
-
Try this:
=YEAR(TODAY()) - YEAR([Job start date]@row) - IF(DATE(YEAR([Job Start Date]@row), MONTH(TODAY()), DAY(TODAY())) < [Job Start Date]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Job start date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Job start date]@row), MONTH(TODAY()) - MONTH([Job start date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Job start date]@row) < 0, 30 + DAY(TODAY()) - DAY([Job start date]@row), DAY(TODAY()) - DAY([Job start date]@row)) + " days"
-
Hi Paul,
That worked a treat, thanks so much for your help!
-
Happy to help. 👍️
-
Hello,
To anyone who used this formula and you were still getting "Invalid Data Type", there is a simple fix.
The column you are pulling from needs to be formulated as a date column and not the default :)
-
@Princess M. Thanks for the note! You are absolutely correct. All of the above is under the assumption that you are referencing a date type column. If for whatever reason you needed to use a text/number type column, my suggestion is that you use a helper (date type) column to convert the text string into a usable date and then reference this helper column in the above.
-
What if I have the Start and End dates and need to find the length?
-
@maltaee I'm not sure I understand what you are asking. The above should give you the duration between a start and end date.
-
Thanks, Paul. I don't see "End Date" in the formula that I can replace both with my Start and End date fields in the sheet.
-
@maltaee You would replace each of the TODAY() functions with the reference to your End Date column.
-
=YEAR([Planned End Date]) - YEAR([Planned Start Date]@row) - IF(DATE(YEAR([Planned Start Date]@row), MONTH(Planned End Date), DAY(Planned End Date), < [Planned Start Date]@row, 1, 0) + " years " + IF(MONTH([Planned End Date]), - MONTH([Job start date]@row) < 0, 12 + MONTH([Planned End Date]), - MONTH([Planned Start Date]@row), MONTH([Planned End Date]), - MONTH([Planned Start Date]@row)) + " months " + IF(DAY([Planned End Date]), - DAY([Planned Start Date]@row) < 0, 30 + DAY([Planned End Date]) - DAY([Planned Start Date]@row), DAY([Planned End Date]) - DAY([Planned Start Date]@row)) + " days"
It is not working yet, but I'm guessing like the above.
-
@maltaee You are missing the @row references.
-
Still incorrect argument.
=YEAR([Planned End Date:]@row) - YEAR([Planned Start Date:]@row) - IF(DATE(YEAR([Planned Start Date:]@row), MONTH([Planned End Date:]@row), DAY([Planned End Date:]@row), <[Planned Start Date:]@row, 1, 0) + " years " + IF(MONTH([Planned End Date:]@row), -MONTH([Planned Start Date:]@row) < 0, 12 + MONTH([Planned End Date:]@row), -MONTH([Planned Start Date:]@row), MONTH([Planned End Date:]@row), -MONTH([Planned Start Date:]@row)) + " months " + IF(DAY([Planned End Date:]@row), -DAY([Planned Start Date:]@row) < 0, 30 + DAY([Planned End Date:]@row) - DAY([Planned Start Date:]@row), DAY([Planned End Date:]@row) - DAY([Planned Start Date:]@row)) + " days")
-
@maltaee Double check your syntax follows the same syntax as the formula provided. Right off I see there is a closing parenthesis at the end of yours when there shouldn't be which means that at least one has been removed from the middle somewhere. I also see at least one comma tucked in where it shouldn't be after the first DATE function.
-
Thanks so much, Paul.
It is working but not showing the correct length.
=YEAR([Planned End Date:]@row) - YEAR([Planned Start Date:]@row) - IF(DATE(YEAR([Planned Start Date:]@row), MONTH([Planned End Date:]@row), DAY([Planned End Date:]@row)) < [Planned Start Date:]@row, 1, 0) + " years " + IF(MONTH([Planned End Date:]@row) - MONTH([Planned Start Date:]@row) < 0, 12 + MONTH([Planned End Date:]@row) - MONTH([Planned Start Date:]@row), MONTH(TODAY()) - MONTH([Planned Start Date:]@row)) + " months " + IF(DAY([Planned End Date:]@row) - DAY([Planned Start Date:]@row) < 0, 30 + DAY([Planned End Date:]@row) - DAY([Planned Start Date:]@row), DAY([Planned End Date:]@row) - DAY([Planned Start Date:]@row) + " days")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!