Tally Number of Years with a Starting Date

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

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.

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

Would dividing by 364.25 work?

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

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

Are you using a date type column?

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

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

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

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.

=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), “")