How to calculate length of service?

RWG

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!

Paul Newcome

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"

Paul Newcome

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"

RWG

Hi Paul,

That worked a treat, thanks so much for your help!

Paul Newcome

Happy to help. 👍️

Princess M.

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 :)

Paul Newcome

@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.

maltaee

https://community.smartsheet.com/discussion/comment/326939#Comment_326939

What if I have the Start and End dates and need to find the length?

Paul Newcome

@maltaee I'm not sure I understand what you are asking. The above should give you the duration between a start and end date.

maltaee

https://community.smartsheet.com/discussion/comment/366448#Comment_366448

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.

Paul Newcome

@maltaee You would replace each of the TODAY() functions with the reference to your End Date column.

maltaee

https://community.smartsheet.com/discussion/comment/366452#Comment_366452

=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.

Paul Newcome

@maltaee You are missing the @row references.

maltaee

https://community.smartsheet.com/discussion/comment/366465#Comment_366465

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")

Paul Newcome

@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.

maltaee

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")