Formula Error - trying to subtract 1 day.

I'm trying to calculate an expiration date and have the resulting number be X years later, but one day prior to the original date. The formula is: =DATE(YEAR([CV Date Signed]@row) + 2, MONTH([CV Date Signed]@row), DAY([CV Date Signed]@row) - 1)

This worked fine on Row 1, where the CV Date Signed date was 2/15/22. But on row 2, where the date was 05/01/22, I got an INVALID VALUE error. I took out the "-1" at the end, and it worked fine.

How do I write the formula so that Smartsheet knows to -1 to April 30th instead of May 0?

Find more posts tagged with

Accepted answers

Michael Culley

Also not sure how precise you need to be but couldn't you also just do =[cv date signed]@row + 729 ? Which is 2 years minus 1 day.

All comments

Michael Culley

Not sure how to account for some months that have 31 days but here's an idea of what you could do.

=DATE(YEAR([cv date signed]@row) + 2, MONTH([cv date signed]@row), IF(DAY([cv date signed]@row) - 1 = 0, 30, DAY([cv date signed]@row)))

I suppose you could also do an if statement checking if month is Jan, Mar, May etc and if Day -1 =0, do 31 for those months.

Michael Culley

Also not sure how precise you need to be but couldn't you also just do =[cv date signed]@row + 729 ? Which is 2 years minus 1 day.

pamwats

That worked. Simpler formula is the better option :)

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.