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

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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,

General Strategy

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.

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.