I've searched multiple questions on how to calculated years from start date.

Julius Aguila

I've tried multiple calculations and all I want is to calculate the years of service. I can use the conditional formatting to note milestones 5, 10, 15, years etc. I did confirm the Start Date field is formatted as a date and getting #INVALID DATA TYPE.

Julius Aguila

This formula worked - =(TODAY() - [Start Date]@row) / 365.25

Paul Newcome

How are your dates being populated?

Heather Duff

Hi @Julius Aguila ,

Try this:

=IF(YEARDAY([start date]@row) > YEARDAY(TODAY()), YEAR(TODAY()) - YEAR([start date]@row) - 1, YEAR(TODAY()) - YEAR([start date]@row))

YEARDAY calculates the day number of the year (for example, December 31 is day 365; January 1 is day 1). This makes things a little cleaner than using MONTH and DAY.

Let me know if it works!

Best,

Heather

Julius Aguila

@Paul Newcome I imported them from an excel sheet. They are in a date format in the column

Paul Newcome

Let's try a little testing just to be sure...

Insert a temporary checkbox column and use this formula:

=IF(ISDATE([Start Date]@row), 1)

Are there any rows that are unchecked?

Julius Aguila

This formula worked - =(TODAY() - [Start Date]@row) / 365.25

Julius Aguila

Thank you all for the input. I don't know why it didn't work the first time but realized I had =(TODAY() - [Start Date]@row)1 / 365.25 which the 1 was from another formula. Once removed, it worked.