How do I get the exact number of Years based on the date column (no more rounding up)?

Hi there! Can anyone kindly please help me with this - I'm new to SmartSheets and having trouble working out formulas…

In one column I have dates that Volunteers started volunteering with our company. The second column I used the following formula to get the number of years that it totals:

=YEAR(TODAY()) - YEAR([Vol Start Date]@row)

However, it keeps rounding up - e.g. if they have done 3 years 9 months it says in column 2 that it's 4 years.

I'm setting up an automation to flag when someone hits a specific milestone e.g. 5 years, so am wanting it to either show the year and month (fine to be decimal places, e.g. 3.75) / or if it's only showing year for it to be exact and doesn't round up. Do you know a formula for how I might go about this?

Thanks so much for your help!

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

dates

Accepted answers

Paul Newcome

Give this a try:

=(YEAR(TODAY()) - YEAR([Vol Start Date]@row)) - IF(TODAY() <= DATE(YEAR(TODAY()), MONTH([Vol Start Date]@row), DAY([Vol Start Date]@row)), 1, 0)

All comments

Paul Newcome

Give this a try:

=(YEAR(TODAY()) - YEAR([Vol Start Date]@row)) - IF(TODAY() <= DATE(YEAR(TODAY()), MONTH([Vol Start Date]@row), DAY([Vol Start Date]@row)), 1, 0)

mary09876

Thanks so much for your help, Paul! Just gave this a try but still wants to round up the date unfortunately. Is there anything extra I might need to do to get to work? Thanks!

Paul Newcome

Are you able to provide a screenshot of some of the dates?

mary09876

Hi Paul! So sorry looked at this with fresh eyes this morning and realised I hadn't set the new formula for all the cells in the column, rookie mistake!

Thanks so much as your formula it is working exactly as hoped, I really appreciate it!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.