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)))