Column Formula for a future date, 3 months before annual start date
Hello everyone,
I have a column formula that creates a date in the future based on [Start Date] while simultaneously avoiding child rows:
=IF(AND(Hierarchy@row = 0), IF(MONTH([Start Date]@row) >3, DATE(YEAR([Start Date]@row) +1, MONTH([Start Date]@row) -3, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), 12 + MONTH([Start Date]@row) -3, DAY([Start Date]@row))), IF(Hierarchy@row > 0, "", [StartDate]@row))
The purpose is to trigger an action due date 3 months before the [End Date]. Now the majority of these [Start Date] rows are for a single year. However, for those rows that have an [End Date] greater than one year, I would like to create a new column that would generate this same formula, but for that 2nd (or more) following year(s). The formula works when the portion "DATE(YEAR([Start Date]@row) +1," is replaced with "+2", but not for those [Start Date] rows that are Jan, Feb, Mar. Then the result is the exact date as the original formula.
Hoping to get that 2nd date to display as 10/16/25, and then a 3rd for 10/16/26. What is the method to get this formula to generate for 2 years, 3 years, 4 years, etc., from that [Start Date] when the Months are Jan, Feb, and Mar.?
Thank you in advance.
Patrice
Best Answer
-
Hi @PEvansLC,
we can't directly iterate over years or dynamically generate column values based on another column's multi-year span directly within a single formula. However, you can achieve your goal by setting up a series of columns that calculate these future dates based on the initial start date. For each additional year, you would have a new column calculating the next review date. Let's outline a strategy for the first few years, assuming you might have up to a 5-year term as you mentioned.
General Strategy
Determine Review Dates: For each year, calculate the review date by subtracting 3 months from the start date. If the month of the start date is Jan, Feb, or Mar, you'll need to adjust the year backward appropriately since subtracting 3 months would land in the previous year.
Handle Jan/Feb/Mar Specifically: For months Jan, Feb, and Mar, subtract an additional year from the year part of the date since subtracting 3 months from these months will go into the previous year.
Active Status Check: Incorporate a check for the [Status] column to ensure the calculation only applies to rows with an "Active" status.
Example Formulas for Yearly Review Dates
Year 1 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row) - 1, MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
Year 2 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
- For each subsequent year (3rd year, 4th year, etc.), you would create a new column and adjust the formula accordingly by adding to the year calculation.
- The formulas check for the
[Status]
column being "Active" and adjust dates for Jan, Feb, and Mar by either subtracting from or adding to the year, depending on the context. - Ensure that for each formula, you adjust the calculation based on the specific year you're targeting.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @PEvansLC
Can you please confirm the below simplified is your actual use case:
Working on to modify the formula to account for the first year, second year, and so on
Regards.
Huma
Community Leader
-
@Humashankar It's probably not that simple. [Start Date] is for a contract date. The [End Date] is the last date of the approved term. Term can be for one year, or up to 5 years. Regardless if term is more than one year, an annual review must be performed at 3 months before that [End Date] Month/Day for current year. Meaning, [Start Date] = 11/2/21, need to perform that review on 8/2/22, 8/2/23, 8/2/24, and so on. Hoping to create columns to represent these individual year ranges.
So, If [Status] = "Active", and date range for the [FY Review Date] Column = 12/1/23-11/30/24, the [FY Review Date]@row should calculate to be the same Day in the [Start Date]@row, AND 3 months less than the [Start Date] month, while ensuring that the Year is within the specified range for the [FY Review Date] column.] If [Status} <> Active, show blank. It get tricky when dealing with Jan/Feb/Mar, because it then needs to be the preceding year.
I've tried to just do 9 months ahead of [Start Date], but cannot get that to calculate more than one year past the start date. Any insight is appreciated.
-
Hi @PEvansLC,
we can't directly iterate over years or dynamically generate column values based on another column's multi-year span directly within a single formula. However, you can achieve your goal by setting up a series of columns that calculate these future dates based on the initial start date. For each additional year, you would have a new column calculating the next review date. Let's outline a strategy for the first few years, assuming you might have up to a 5-year term as you mentioned.
General Strategy
Determine Review Dates: For each year, calculate the review date by subtracting 3 months from the start date. If the month of the start date is Jan, Feb, or Mar, you'll need to adjust the year backward appropriately since subtracting 3 months would land in the previous year.
Handle Jan/Feb/Mar Specifically: For months Jan, Feb, and Mar, subtract an additional year from the year part of the date since subtracting 3 months from these months will go into the previous year.
Active Status Check: Incorporate a check for the [Status] column to ensure the calculation only applies to rows with an "Active" status.
Example Formulas for Yearly Review Dates
Year 1 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row) - 1, MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
Year 2 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
- For each subsequent year (3rd year, 4th year, etc.), you would create a new column and adjust the formula accordingly by adding to the year calculation.
- The formulas check for the
[Status]
column being "Active" and adjust dates for Jan, Feb, and Mar by either subtracting from or adding to the year, depending on the context. - Ensure that for each formula, you adjust the calculation based on the specific year you're targeting.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you @Humashankar for the detailed explanation. I figured that additional columns would be needed for the subsequent future years. Was just hopeful that a dynamic formula was possible.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!