MAXIFS to take the MAX of two columns

[megera716]

I am trying to forecast a schedule of the construction payments in multiple new locations.

In L634, I have this formula to provide an estimated next invoice date which works great until we start getting invoices because you'll see it says the next estimated invoice date is 3/1/24 but we actually received an invoice on 3/31/24 so obviously the "next" one can't be coming on 3/1/24. I would like the formula to return the MAX of the estimated next invoice date or the MAX of the actual invoice date, if that is greater, PLUS 1 month.

=IF(D634="TI",IF(COUNTIFS($J$2:$J634,J634,$E$2:$E634,"Construction Payment*")=1,EDATE(K634,-6),IF(ISNUMBER(SEARCH("Construction Payment",E634)),EDATE(MAXIFS($L$2:$L633,$J$2:$J633,J634,$E$2:$E633,"Construction Payment*"),1),"")))

I tried nesting a MAX of columns L:M in there but it throws an error. Column J is the Location and I need that criteria. Column K is the date we expect the construction to be complete and we plan on construction starting 6 months before that date (hence the EDATE - 6.

If I base the MAXIF on Column M, then when I have a location that has not received any invoices yet, I get "1/31/1900":

 

[shift-del]

I will not recreate the data.
But if you have trouble with nesting function try to un-nest them and use helper columns.