How to change the year on a date based on a condition

Carroll Wall

SHORT VERSION: How do I write a formula to pull a date from one column and update JUST the year of that date if a condition is met in a separate column

Very Long explanation:

I have a data base of contracts that must be evaluated either once a year or every 90 days depending on risk level.

The initial evaluation due date is based off the start date of the contract (Either start date + 90 days for High risk or, start date plus 365 days for Low or Medium risk) After the first evaluation is recorded, the due date is set by the date of the previous evaluation. These formulas are fairly easy to set up, and I put them in helper columns, see below.

The issue I am having is that as the data base is being filled, some contracts have start dates many years past, so adding just 90 or 365 days ends up with due dates also may years in the past.

I am trying to create a formula that works as follows:

If Current Risk Level is HIGH, return High Risk helper with the year 2023 (current year), Otherwise return Low Med Risk Helper with year 2023 (current year)

(Please note that the Current Evaluation Date column is shown with an incorrect formula in it so the dates are not correct. The 2 helper columns are working as desired, Also, all columns but the right-most are formatted as date columns)

High Risk Helper formula

=IF(AND([Previous Evaluation Date]@row <> "", [Current Risk Level]@row = "High"), [Previous Evaluation Date]@row + 90, [Contract Start Date]@row + 90)

Narrative

If Previous Eval Date is not blank AND Current Risk Level is High, then return Previous Eval Date +90 days. Otherwise return Contract Start Date + 90 days

 

Low Med Risk Helper Formula

=IF(ISBLANK([Previous Evaluation Date]@row), ([Contract Start Date]@row + 365), [Previous Evaluation Date]@row + 365)

Narrative

If Previous Eval Date is blank, then return Contract Start Date + 365 days. Otherwise return Previous Eval Date + 365 days

This is the formula I am fiddling with but I don’t know how to nest the elements so I am getting syntax error

=IF([Current Risk Level]@row = "High"), [High Risk Helper]@row and then some how insert this bit (IF(YEAR([High Risk Helper]@row) <= 2023, DATE(2023, MONTH([High Risk Helper]@row), DAY([High Helper]@row), otherwise return Low Med Risk Helper]@row inserting this bit IF(YEAR([Low Med Risk Helper]@row) <= 2023, DATE(2023, MONTH([Low Med Risk Helper]@row), DAY([Low Med Risk Helper]@row)

 

Any thoughts on how to build/nest that current evaluation date formula would be highly appreciated. I am also open to a total re-do of all the columns and formulas if it will achieve the goal of creating due dates in the current year based on risk levels .

Thank you!

Paul Newcome

What would you want to do if the year in the helper columns is more than 2023?

Based on what you currently have in your post, give this a try:

=DATE(YEAR(TODAY()), MONTH(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0)), DAY(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0)))

What this says is to use the current year (can be adjusted once we answer the above question).

Then evaluate the date columns to choose which one to use.

Then add 90 days if the Risk is "High".

Then strip the MONTH out and use that for the month portion of our DATE function.

Repeat MONTH steps for DAY to use in the day section of the DATE function.

Carroll Wall

Hi Paul,

Thanks for taking the time to dig into this,

I am using the helpers to check for a previous eval and then add days according to risk level.

The one I am having trouble with is check for risk level then return a date from the desired column, changing the year to 2023 if it is before that, otherwise no change.

If I am understanding correctly, it looks like you are proposing to do away with the helper columns. Which is no problem.

TO specifically answer your question. If the helper columns return a date in a future year, that is fine. I don't want it to change in the Current Eval Date. That is, I don't need to force TODAY's year.

I have been using "less than or equal to 2023" and it works to preserve those future year dates.

<= 2023, DATE(2023, MONTH([Low Med Risk Helper]@row), DAY([Low Med Risk Helper]@row)), [Low Med Risk Helper]@row))

Meaning if the helper date is less than 2023, change it to 2023. otherwise do not change it.

Of course the issue is: on Jan 1 I need to go in and change that to 2024, etc. Which in the scheme of things is not a lot of maintenance. but, if I didn't have to .....

Did that help to clarify?

Thanks again!

C

Paul Newcome

That does clear it up and can be handled like so:

=DATE(IF(YEAR(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0))< YEAR(TODAY()), YEAR(TODAY()), YEAR(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0))), MONTH(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0)), DAY(IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0)))

Basically we take this:

IF([Previous Evaluation Date]@row <> "", [Previous Evaluation Date]@row, [Contract Start Date]@row) + IF([Current Risk Level]@row = "High", 90, 0)

And strip the year

YEAR(IF(..........) + IF(..........))

Then we say that if it is less than the current year, output the current year, otherwise use whatever year is generated.

IF(YEAR() < YEAR(TODAY()), YEAR(TODAY()), YEAR())