Subtracting Months from a date, Error #Invalid Value

I have three columns. One with a date 7/1/2027 in date format. One with a number representing the number of months I wish to subtract from the date column. Third column is a date column with the following equation:

=DATE(YEAR([Column4]@row) - INT((MONTH([Column4]@row) - VALUE(Factor1)) / 12), MOD(MONTH([Column4]@row) - VALUE(Factor1) - 1, 12) + 1, DAY([Column4]@row))

The equation was also asked in Smartsheet AI assistant can gave this same equation. However, it works fine as long as I don't subtract so many months I end up in the previous year. Subtract 1-6 works. Subtract 7 or more get the error. How can I correct the equation so that it always works.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

Paul Newcome

This should take care of any number of months whether positive or negative:

=DATE(YEAR([Column4]@row) + (FLOOR(MONTH([Column4]@row) + (Factor1 - 1), 12) / 12), IF(MOD(MONTH([Column4]@row) + Factor1, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor1, 12)) + IF(IF(MOD(MONTH([Column4]@row) + Factor1, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor1, 12)) < 0, 12, 0), DAY([Column4]@row))

Garry D

Perfect, thank you as always Paul, we have exchanged items before when I was logged into the community as just Garry I believe.

Paul Newcome

Happy to help. 👍️

Garry D

What syntax would be disallowing your equation to be a Column Formula?

Will Jeffords

Garry and Paul, does this make it "column-able"? I think it just needed "@row" on the Factor1 instances, right?

=DATE(YEAR([Column4]@row) + (FLOOR(MONTH([Column4]@row) + ([Factor1]@row - 1), 12) / 12), IF(MOD(MONTH([Column4]@row) + [Factor1]@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + [Factor1]@row, 12)) + IF(IF(MOD(MONTH([Column4]@row) + [Factor1]@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + [Factor1]@row, 12)) < 0, 12, 0), DAY([Column4]@row))

Garry D

Unparseable

Will Jeffords

Maybe my brackets were not needed? Try this one… (if this wrong, likely need Paul :))

=DATE(YEAR([Column4]@row) + (FLOOR(MONTH([Column4]@row) + (Factor1@row - 1), 12) / 12), IF(MOD(MONTH([Column4]@row) + Factor1@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor1@row, 12)) + IF(IF(MOD(MONTH([Column4]@row) + Factor1@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor1@row, 12)) < 0, 12, 0), DAY([Column4]@row))

Paul Newcome

I copy/pasted my formula from a working sheet. It may be that the column names I used were not the ones being used in your sheet.

@Will Jeffords is also correct that you need to use @row instead of a specific row number to be able to use it as a column formula.

I think the newest issue is

Factor1@row

Should just be

Factor@row

Paul Newcome

=DATE(YEAR([Column4]@row) + (FLOOR(MONTH([Column4]@row) + (Factor@row - 1), 12) / 12), IF(MOD(MONTH([Column4]@row) + Factor@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor@row, 12)) + IF(IF(MOD(MONTH([Column4]@row) + Factor@row, 12) = 0, 12, MOD(MONTH([Column4]@row) + Factor@row, 12)) < 0, 12, 0), DAY([Column4]@row))

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.