Formula for years and months between two dates - three dates

Jason B Meidl

Hey Smartsheet!

This is regarding service/employment time for employees and I want it in years and months. Right now we have one that works for just years and that formula is: =IF(DATE(YEAR(TODAY()), MONTH([Start Date]33), DAY([Start Date]33)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]33), YEAR(TODAY()) - YEAR([Start Date]33) - 1)

It works great!

but now, I want to include former employees on this same sheet if possible. So to calculate their service time I would need to focus on the column "Last Day" instead of Today date to get an accurate service time. So I'm wondering if it's possible to keep former and current employees on the same sheet or not.

My hope is I could keep all of them on the same sheet and use the same "Years of Service" column for both current and former employees to see their years and months of service by doing a formula that would go something like "Start Date" to "Last Day" and IF there is no "Last Day" (because the employee is current) then it would go to "Start Date" to Today.

Is this possible? If not I would just need a formula for years and months from "Start Date" to "Last Day" for my former employee sheet and formula to update my current employee sheet that would calculate months and years between "Start Date" and Today.

Thank you for your help!

Paul Newcome

Replace each of the TODAY() references with

IF([Last Day]@row <> "", [Last Day]@row, TODAY())

Jason B Meidl

Hey Paul,

I tried adding that to my forumula and I don't know if I did it correctly but it doesn't seem to be working. I did this for a former employee who had a [Start Date] of 03/29/17 and [Last Day] of 05/29/20 and the formula comes up with 0 when I'm hoping it would come up with 3 years 2 months. Did your solution include months?

here is how I added your solution to my formula please correct if wrong: =IF(DATE(YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())), MONTH([Start Date]38), DAY([Start Date]38)) < IF([Last Day]@row <> "", [Last Day]@row, TODAY()), YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]38), YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]38) - 1)

Thanks!

Paul Newcome

I just saw the bit where your existing formula was working, so I made the suggestion on how to account for a [Last Day].

To get the number of months, we would need a different formula. Let's see if we can figure something out.

The years are easy enough.

YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]@row)

Then we subtract 1 from that if today is less than the start date/earlier in the year (same logic you have just a different way to write it).

IF(IF([Last Day]@row <> "", [Last Day]@row, TODAY())< DATE(YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())), MONTH([Start Date]@row), DAY([Start Date]@row)), 1)

Which gives us:

YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]@row) - IF(IF([Last Day]@row <> "", [Last Day]@row, TODAY())< DATE(YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())), MONTH([Start Date]@row), DAY([Start Date]@row)), 1) + " years " +

Now we need to work out our months. We can use a similar logic. If the current/last month is greater than or equal to the starting month, then a straight subtraction will work.

IF(MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY())>= MONTH([Start Date]@row), MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY()) - MONTH([Start Date]@row), ...................)

But if it that is false (current/last month is less than start month) then we need to do 12 - (start month - end month).

IF(MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY())>= MONTH([Start Date]@row), MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY()) - MONTH([Start Date]@row), 12 - (MONTH([Start Date]@row) - IF([Last Day]@row <> "", [Last Day]@row, TODAY()))) + " months"

Put everything together like so:

=YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]@row) - IF(IF([Last Day]@row <> "", [Last Day]@row, TODAY())< DATE(YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())), MONTH([Start Date]@row), DAY([Start Date]@row)), 1) + " years " + IF(MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY())>= MONTH([Start Date]@row), MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY()) - MONTH([Start Date]@row), 12 - (MONTH([Start Date]@row) - IF([Last Day]@row <> "", [Last Day]@row, TODAY()))) + " months"

And that should do the trick.

Jason B Meidl

Hey Paul,

Thank you so much for all of this! I really appreciate your time. However, something still isn't right. I started at the top and your first formula to count years works spot on. Once I get to your third formula though right before brining months into the formula its comes up as "#UNPARSEABLE"

so anything after that comes up the same way.

The start date of 10/23/17 is working by our old formula =IF(DATE(YEAR(TODAY()), MONTH([Start Date]28), DAY([Start Date]28)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]28), YEAR(TODAY()) - YEAR([Start Date]28) - 1)

Start Date 6/18/20 is working by yours YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]@row)

the 9/16/15 start date just has your third formula. So it should show 6 years but...

Lastly the 9/21/21 start date, has an end date just two months later so I tried using your last formula for that one and it come up unparseable

Paul Newcome

So this does not work?

=YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())) - YEAR([Start Date]@row) - IF(IF([Last Day]@row <> "", [Last Day]@row, TODAY())< DATE(YEAR(IF([Last Day]@row <> "", [Last Day]@row, TODAY())), MONTH([Start Date]@row), DAY([Start Date]@row)), 1) + " years " + IF(MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY())>= MONTH([Start Date]@row), MONTH(IF([Last Day]@row <> "", [Last Day]@row, TODAY()) - MONTH([Start Date]@row), 12 - (MONTH([Start Date]@row) - IF([Last Day]@row <> "", [Last Day]@row, TODAY()))) + " months"