Having issues with Year formulas

REC

I have tried several year formulas, none of which I have found to be accurate. I have tried dividing the days by 365 and using rounding but the rounding doesn't work for half of the year. The current solution I'm working with works except when the year advances. If I have a start date of 6/1/2024 and a completion date of 2/5/2025 the formulas shows 1 yr and when I add the months and day formulas it will show 1Y 8M 4D. The correct answer I would expect is 8M 4D. I am using an IF statement to not show the Year when the year is below 1. The operators I've tried are <> 0, >1, = > 1.
I'm not stuck on this formula so if there is a better way to get accurate year count that can work within a year and span multiple years, I'm all for it.

Here are a couple of my year formulas I've tried.

=(IF(YEAR([Date Completed]243) - YEAR(Age243) > 1, YEAR([Date Completed]243) - YEAR(Age243) + " Y ", IF(YEAR([Date Completed]243) - YEAR(Age243), YEAR([Date Completed]243) - YEAR(Age243) + " Y ", "")))

=IF(YEAR([Date Completed]243) - YEAR([date]243) >= 1, YEAR([Date Completed]243) - YEAR([date]243), "")

Here's examples of the dates used for testing.

2/5/2024 to 1/1/2026 shows 2 years. Should show 1 year.

2/5/2024 to 12/20/2024 shows 0 years as it should.

Here is the rest of my date formula when added to the year provides Month and Day counts.

+ IF(MONTH([Date Completed]243) - MONTH([Start Date]8) < 0, 12 + MONTH([Date Completed]243) - MONTH([Start Date]8), MONTH([Date Completed]243) - MONTH([Start Date]8)) + " M " + IF(DAY([Date Completed]243) - DAY([Start Date]8) < 0, 30 + DAY([Date Completed]243) - DAY([Start Date]8), DAY([Date Completed]243) - DAY([Start Date]8) + " D "

Thank you in advance for your assistance.

Paul Newcome

Try this:

=(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y " + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"

Paul Newcome

Does this work?

=IF(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0) > 0, (YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y ") + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"

Amit Wadhwani

Seems to be some problem with how the dates are formatted. Can you check if all your columns are formatted as mm/dd/yyyy?

REC

The date columns are the same format MM/DD/YY. I'm not sure how to format them differently between columns.

Paul Newcome

Try this:

=(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y " + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"

REC

HI Paul, your year calculation works well for me. I tried testing how to not show 0Y when the year was less than 1. None of what I tried worked in association with your formula. If it's not possible and I have to show 0Y then I'll just move forward. Any ideas or solution to not show 0Y if the year is less than 1?
Thank you for your assistance!

Paul Newcome

Does this work?

=IF(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0) > 0, (YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y ") + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"

REC

Outstanding! It works perfectly. :D

Thank you!

Paul Newcome

Happy to help. 👍️