Formula for Advancing a Date Field Based On A Frequency Selection

Andy Lidbeck

Here is another one for all of you formula wizards!

Hre is a screen capture of the fields I am working with for this problem.

"Copy Date" is a static entry, in which I manually enter a date.

"Trigger Date" is a date field field with a function that is calculating a date based on the values contained within "Copy Date".

"Frequency" is a pick list, which is used in the formula to determine how far ahead the date should be advanced.

My current formula

=IF(Frequency@row = "Annually", (DATE(YEAR([Copy Date]@row) + 1, MONTH([Copy Date]@row), DAY([Copy Date]@row))),

IF(Frequency@row = "Monthly", (DATE(YEAR([Copy Date]@row), MONTH([Copy Date]@row) + 1, DAY([Copy Date]@row))))))

My formula works great when the frequency is annually. The formula breaks when I add in the config for monthly, specifically when I try to advance from December to January. I believe I have to use an IFERROR statement.

This iferror statement works fine, from my testing.

=IFERROR(DATE(YEAR([Copy Date]@row), MONTH([Copy Date]@row) + 1, DAY([Copy Date]@row)), DATE(YEAR([Copy Date]@row) + 1, 1, DAY([Copy Date]@row)))

So here is my question, how can I use that IFERROR statement within my IF statement so that I can have the logic that differentiates between Annually, Monthly, etc.? Never used one before, and just having a bit of trouble getting the logic correct with it.

Toufong Vang

Hi, Andy.

There are several ways to handle this, but, since you're incrementing by only 1 month, then this might be simplest.

In your DATE() function, use the following for your month element.

IF( MONTH([COPY Date]@row + 1) = 13, 1, 12)