Subtracting Months from Target Date
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!
Best Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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)), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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?
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
-
This is a helpful link for troubleshooting formulas:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!