Calculate a date with reference to multiple cells and with multiple conditions
Solution 1:
So the core mechanic you want is:
=DATE(YEAR(C1), MONTH(A1), DAY(A1))
We can't just say =year(C1) + month(a1) + day(a1)
because it would undergo date addition in that context, whereas if you use the =date(year, month, day) method, it correctly interprets what you're going for.
Next we'll want to add the if syntax, which is basically going to be like this:
=if(date1 > date2, "True", "False")
The complete equation will essentially take the previous new date, and calculate it twice (because it can't self-reference easily), once in the conditional and again for the result if there wasn't a rule violation:
=if(DATE(YEAR(C1), MONTH(A1), DAY(A1)) > B1, "Error!", DATE(YEAR(C1), MONTH(A1), DAY(A1)))