Adding months to a date (keep getting errors)

I've tried so many formulas listed on these forums and none seem to work for me so I thought I would pose my actual data and see what help I can get.

I have a Hire Date column that is formatted as a date column. In a separate column I want add 12 and 18 months respectively and return that date in the appropriate column. I do not want a helper column, just a straight formula.

A formula and it's associated error is listed below the image in case it is helpful.

Using =DATE(YEAR(Hire Date@row)+1, MONTH(Hire Date@row), DAY(Hire Date@row)) provides an UNPARSEABLE error

Find more posts tagged with

Accepted answers

All comments

Victoria_Indimar

Hi @coloradolime ! Re-reading your formula, it looks like you're just missing the square brackets around your "Hire Date". It should be [Hire Date]@row within the formula. Please try that and let us know if it works for you!

=DATE(YEAR([Hire Date]@row)+1, MONTH([Hire Date]@row), DAY(Hire Date]@row))

coloradolime

When entering that I still get the UNPARSEABLE error

TVang

Hi, @coloradolime, give this a try.

12 Month Mark =DATE(YEAR([Hire Date]@row) + 1, MONTH([Hire Date]@row), DAY([Hire Date]@row))
18 Month Mark =DATE(YEAR([Hire Date]@row) + 1, 18 - MONTH([Hire Date]@row), DAY([Hire Date]@row))

EXPLANATION
12 Months = 1 year ... So, for 12 Month Mark, your formula is straight forward. You'll need to get the YEAR() from the hire date, and then add 1 to it: YEAR([Hire Date]@row) + 1

And the formula is:
12 Month Mark = DATE(YEAR([Hire Date]@row) + 1, MONTH([Hire Date]@row), DAY([Hire Date]@row))

18 Month Mark is a little trickier. You have to account for the 6 months and 1 year. From the formula above, you already have the expression you need to calculate the year.

As for months, "the month 18 months from the hire date" is the same month as "18 months minus the month of the hire date". I.e., 18 - MONTH([Hire Date]@row)
Thus, the formula is:
18 Month Mark = DATE(YEAR([Hire Date]@row) + 1, 18 - MONTH([Hire Date]@row), DAY([Hire Date]@row))

Here's where you can get more information about Smartsheet functions.
https://help.smartsheet.com/functions

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.