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!
