Tally Number of Years with a Starting Date

@EmilyJMorgan

I'm new to SmartSheets, so be gentle .  I want to insert a formula that will automatically tally a number of years from a given start date every year.  

Example:  I want to know the number of years a person has been in a given rank.  I have the rank date set at 8/16/17.  Every year on 8/16, I want to automate the column by adding another year to their rank.

Suggestions welcome!

 

 

 

Brian W

Here is a formula I found that should accomplish the task:

=IF(MONTH([Appointed to Rank]@row) > MONTH(TODAY()), YEAR(TODAY()) - 1 - YEAR([Appointed to Rank]@row), IF(AND(MONTH([Appointed to Rank]@row) >= MONTH(TODAY()), DAY([Appointed to Rank]@row) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR([Appointed to Rank]@row), YEAR(TODAY()) - YEAR([Appointed to Rank]@row)))

Paul Newcome

You could also use something along the lines of

 

=INT((TODAY() - [Appointed to Rank]@row) / 365)

 

This will count the number of days between TODAY() and the date in the [Appointed to Rank] column of the same row. It will then divide that number by 365 which will give you how many years it has been. Then by pulling the integer, it will essentially round down to the whole number meaning even if the result is 1.999, it won't show 2 years until it actually hits 2 (which would be the same date just one year later.

Brian W

Hey Paul. I initially thought of a similar formula:

=INT(NETDAYS([Appointed to Rank]9, TODAY(-1)) / 365)

But it doesn't account for leap years...

Paul Newcome

Would dividing by 364.25 work?

@EmilyJMorgan

Sadly, I get an #Unpars message with this formula.  Thank you, however, for trying.

@EmilyJMorgan

Sadly I get an #Invalid message with this formula.  Thank you for trying, however.

Paul Newcome

Are you using a date type column?

@EmilyJMorgan

This could work; however, I need the date to be the "Appointed to Rank" date rather than "Today"--any suggestions for a revision here?

@EmilyJMorgan

I tried both a date column and a text/number column.  No luck with either.

Paul Newcome

=INT((TODAY() - [Column6]@row) / 364.25)

 

I just tested the above formula with various dates and got the results you were wanting. If the Appointed date is more than a year but less than 2, it will show 1. If it's more than 2 but less than 3 then it shows 2, so on and so forth. I put the date in a date type column and the formula in a text type column.

Brian W

365.25 is close but, as you can see from the screenshot, it's just slightly off. In the screenshot, the middle row is dividing by 365.25, but someone who started today in 2000 is showing they have only worked 17 years, instead of 18 years. The bottom row is the more complex accurate formula.

Brian W

=INT(NETDAYS([Appointed to Rank]@row, TODAY(-1)) / 365)

This formula should be close to the desired result you are looking for, if you don’t mind being off by a day occasionally. The first counts the number of days between the Appointed to Rank date and yesterday, and divides by 365 to find the approximate number of years.

Make sure your Appointed to Rank column is a Date, and your calculating column is Text/Number. You will also receive #INVALID DATA TYPE if the date column is blank. You can eliminate that with an IFERROR statement like this:

=IFERROR(INT(NETDAYS([Appointed to Rank]@row, TODAY(-1)) / 365), “")

@EmilyJMorgan

It works!  Thank you all for your help and patience.  I'm sure I will have more questions in the future.