How to calculate length of service?

RWG · 04/21/22

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 · 04/21/22

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 · 04/21/22

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 · 04/22/22

Hi Paul,

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

Paul Newcome · 04/27/22

Happy to help. 👍️

Princess M. · 01/29/23

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 · 01/30/23

@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 · 03/09/23

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 · 03/09/23

@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 · 03/09/23

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 · 03/09/23

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

maltaee · 03/09/23

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 · 03/09/23

@maltaee You are missing the @row references.

maltaee · 03/09/23

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 · 03/09/23

@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 · 03/10/23

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

How to calculate length of service?

Best Answer

Answers

Help Article Resources

Categories