Nested fuormula using Max function

[opal1958]

I am trying to build a nested formula that using max formula:

Column AS Column AT Column AU ... Column BJ
Row 1 07/15/2016 10/11/2016 Need to Solve 10/1/2016
Row 2 blank blank
Row 3 10/15/2016 3/4/2017

I would like column C to be able to do one of the following:

• Take max of column A & B
• If blank read " No date"
• if max date before 10/1/2016 then set to 10/1/2016
• if max date before greater than today then set to today

Here is what i have but only 3 out of the 4 work, what is highlighted in bold will not work.

IF(COUNT(AS2:AT2),MAX(AS2:AT2),IF(ISBLANK(MAX(AS2:AT2)<($BJ$1)),$BJ$1,"No Date"))

 

[opal1958]

ok some how when I upload it jammed together... Column AU is where I want to solve and column BJ holds the 10/1/2016 date. Thanks