Modifying a date based on 2 variables
I would like to create a formula that says
If the value in the "Variable" column is "HIGH" AND the date in "Column 1" has a year less than today's year, return that date with TODAY"S year. Otherwise return that date unchanged.
In other words, dates in the previous years are brought to this year, dates in this year or future years remain unchanged.
The result would look like this (with "Goal Column" being where the formula is)
I have gotten this far:
IF(AND([Variable]@row = "High", magic happens here to look at just the year in First Column). DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row, [First Column]@row)))
(I know I am missing some parentheses in the example, I can fix those. I am looking for the magic sauce in the middle)
Thank you very much for taking a look.
Help Article Resources
Check out the Formula Handbook template!