Column Formula for a future date, 3 months before annual start date

PEvansLC

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

Bassam Khalil

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.

Humashankar

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.

PEvansLC

@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.

Bassam Khalil

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.

PEvansLC

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.