Subtracting Months from Target Date

William Coz

Hello Smartsheet Experts,

I really need your help as I have been banging my head against the wall on this one.

What I am trying to accomplish

I have a "Target Date" Column where a date is being listed as the Go Live Date for a new product.
I have 4 seperate columns that I am counting down the time (Months) till the "Target Date" (T-18 Months, T-12 Months, T-6 Months, T-3 Months, T+4 Months)
I keep trying various formulas to no avail

Formula I have been trying (Example for T-12)

=IF((MONTH([Target Date]@row) - 1) < 1, DATE(YEAR([Target Date]@row) - 1, MONTH([Target Date]@row) - 12 + 12, DAY([Target Date]@row)), DATE(YEAR([Target Date]@row), MONTH([Target Date]@row) - 12, DAY([Target Date]@row)))

Result

When I use this formula for the 6 Months (by replacing the 12 with the 6) it works mostly with years 2023 and 2024 but not below December 2022

What I am hoping to achieve (with your gracious help!)

Thank you all in advance for your help!

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@William Coz

Starting with T-12, you're overthinking it. For T-12 months all you have to do is change the year to one year before. For each date calculation, we will construct the DATE value by finding values for YEAR and MONTH using embedded IF statements.

=DATE(YEAR([Target Date]@row) -1, MONTH([Target Date]@row), DAY([Target Date]@row))

T-18:

=DATE(IF(MONTH([Target Date]@row) < 7, (YEAR([Target Date]@row) - 2), (YEAR([Target Date]@row) -1)), IF(MONTH([Target Date]@row) < 7, (MONTH([Target Date]@row) + 6), (MONTH([Target Date]@row) - 6)), DAY([Target Date]@row))

Logic: Year calc - IF the month of the target date is less than 6 (Jan - June), subtract 2 years from the target date year, otherwise, subtract 1 year from the target year. Month calc - IF the month of the target date is less than 6 (Jan - June), add 6 to the target date month, otherwise subtract 6 from the target month. Day calc - Set the day to the same day as the target date.

With the above, a target date of 6/15/23 results in a T-18 value of 12/15/21, and a target date of 10/1/23 results in a T-18 date of 4/1/22.

(Note: where this get a little messed up is if your target date is the 31st of a month and 18 months before is month without 31 days. Ex. 3/31/24 target date results in T-18 date of 9/31/22 - there is no September 31st, so if you try it, it calculates out to 10/1/22. Not a huge deal, just something to be aware of.)

T-6:

=DATE(IF(MONTH([Target Date]@row) < 7, (YEAR([Target Date]@row) - 1), YEAR([Target Date]@row)), IF(MONTH([Target Date]@row) < 7, (MONTH([Target Date]@row) + 6), (MONTH([Target Date]@row) - 6)), DAY([Target Date]@row))

(Same note as above, regarding 31-day months.)

T-3:

=DATE(IF(MONTH([Target Date]@row) < 4, (YEAR([Target Date]@row) - 1), YEAR([Target Date]@row)), IF(MONTH([Target Date]@row) < 4, (MONTH([Target Date]@row) + 9), (MONTH([Target Date]@row) - 3)), DAY([Target Date]@row))

(Same note as above, regarding 31-day months.)

T+4:

=DATE(IF(MONTH([Target Date]@row) > 8, (YEAR([Target Date]@row) + 1), YEAR([Target Date]@row)), IF(MONTH([Target Date]@row) > 8, (MONTH([Target Date]@row) - 8), (MONTH([Target Date]@row) + 4)), DAY([Target Date]@row))

(Same note as above, regarding 31-day months.)

Edit: Adding test results. I copied and pasted the formulas above into these rows. Calculations look good to me.

Jeff Reisman

Happy I could help.

As far as the error goes, double check your commas, parentheses, "@row" s, etc.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.
The function is missing an argument.
There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

All comments

William Coz

Apologies as the message cut off.

What I am hoping to achieve (with your gracious help!)

Each Column (T-18 Months, T-12 Months, T-6 Months, T-3 Months, T+4 Months) Populates the correct date regardless of the Target Date that is entered.

Jeff Reisman

@William Coz

=DATE(YEAR([Target Date]@row) -1, MONTH([Target Date]@row), DAY([Target Date]@row))

T-18:

With the above, a target date of 6/15/23 results in a T-18 value of 12/15/21, and a target date of 10/1/23 results in a T-18 date of 4/1/22.

T-6:

(Same note as above, regarding 31-day months.)

T-3:

(Same note as above, regarding 31-day months.)

T+4:

(Same note as above, regarding 31-day months.)

Edit: Adding test results. I copied and pasted the formulas above into these rows. Calculations look good to me.

Jeff Reisman

@William Coz

One other note - once you are sure these formulas works, wrap each of these in IFERROR to avoid the #INVALID DATA TYPE error message when the Target Date is blank:

ex.

=IFERROR(DATE(IF(MONTH([Target Date]@row) < 7, (YEAR([Target Date]@row) - 2), (YEAR([Target Date]@row) -1)), IF(MONTH([Target Date]@row) < 7, (MONTH([Target Date]@row) + 6), (MONTH([Target Date]@row) - 6)), DAY([Target Date]@row)), "")

William Coz

Jeff, I am speechless and simply amazed by how amazingly insightful you have been! I honestly cannot thank you enough. Please know you have made, my day, week, and month with this information. I have been chasing this every day for a while.

The explanations are amazing and I totally grasp the concepts you outlined.

Also great tip on the IFERROR item!

Only one follow-up question, for some reason the T-12 (Minus) one-year calculation keeps pulling up # INOCRRECT ARGUMENT error. I have checked the formula a few times to make sure I am entering it correctly, any thoughts?

Jeff Reisman

Happy I could help.

As far as the error goes, double check your commas, parentheses, "@row" s, etc.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.
The function is missing an argument.
There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

William Coz

Perfect thank you Jeff! That was actually helpful to get me to slow down and find the issue. I did find it. Thank you again! You really made a huge difference. Have a great day!

Jeff Reisman

This is a helpful link for troubleshooting formulas:

Formula Error Messages | Smartsheet Learning Center

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.