SharePoint Calculated MAX IF Statement

I am working on a project which is a scheduling tool. Here is the sample data:

1Q15 - 2Q15 - 3Q15 - 4Q15 - My Today - Date Of Last Insp

1/1/15 - 4/6/15 - 8/1/15 - 12/7/15 - 12/2/15 - 8/1/15

1/1/15 - 8/1/15 - 11/7/15 - 12/2/15 - 11/7/15

What I am trying to do is get the MAX date prior to today, so far I have been able to get the MAX date but it exceeded today. Another issue i am facing is that not all records will have dates in all 4 Q fields. Here are some formulas i have tried and any help would be greatly appreciated.

Also this is the array formula that I used in excel"{=MAX(IF(V7:Z7<=TODAY(),V7:Z7,0))}"

=IF(TEXT(MAX(INT([1Q16]),INT([2Q16]),INT([3Q16]),INT([4Q16])),"MM/DD/YYYY")>=Today,TEXT(MAX(INT([1Q16]),INT([2Q16]),INT([3Q16]),INT([4Q16]),>[Today]),"MM/DD/YYYY"),"No Record")

=IF(Max(INT([1Q16]),INT([2Q16]),INT([3Q16]),INT([4Q16])<=Today,TEXT(max(INT([1Q16]),INT([2Q16]),INT([3Q16]),INT([4Q16]))<=[Today]),"MM/DD/YYYY"),"No Record")

=MAXA([1Q16],[2Q16],[3Q16],[4Q16]<[My Today])

=if(or(isblank([4q16]),[3Q16],[4q16]<=[my today]))),[4q16], if(or(isblank([3q16]),[2Q16],[3q16]<=[my today]))),[3q16], if(or(isblank([2q16]),[1Q16],[2q16]<=[my today]))),[2q16], if(or(isblank([1q16]),”No Record”,[1q16]<=[my today]))),[1q16],”No Record”))))