Testing multiple due dates against completion dates

Hey Community members

I have a sheet that is tracking reporting compliance. Most of the reports only have ONE due date (typically reported annually ie 30th June), but there is a number that requires monthly and quarterly reporting.

To allow our users to enter up to 12 dates for a reporting cycle we have 12 fields to capture these dates (ie [1st Date], [2nd Date], [3rd Date] etc). The users flags that the reported has been completed by entering the date of completion (ie [1st Completed], [2nd Completed], [3rd Completed] etc). This completion date is also used to calculate if the completion was before or after the due date.

I have found that the report builder has limited our ability to test all 12 dates against the 12 completion dates and therefore have resorted to creating a reference cell [Group Status] within the sheet to automatically identify if anything is overdue.

To help explain the process I will use a quarter reporting cycle as an example:

So we have a report that is due 31/3/19 (Q1), 30/06/19 (Q2), 30/09/19 (Q3) & 31/12/19 (Q4)

If we check to see if anything is overdue as at today (13/05/19), we are only interested if the 31/3/19 [1st Date] report has been completed or overdue if there is not completion date [1st Completed]. Down the track if the date was 3/11/19, then we need to ensure 31/3, 30/6 & 30/9 have been completed, by testing the values in [1st date] : [1st Completed] , [2nd date] : [2nd Completed], [3rd date] : [3rd Completed].

The following formula (below) is use to test for any overdue dates, if found the text “over” is appended to the cell [Group Status]. When reporting if [Group Status] contains “over”, then that row is included in the report.

Now the following formula works, but KILLS the performance.

Formula in [Group status]

=IF(AND(AND(ISDATE([1st Date]1), [1st Date]1 < TODAY(), ISBLANK([1st Completed]1))), "1.Over", " 1.Not") + IF(AND(AND(ISDATE([2nd Date]1), [2nd Date]1 < TODAY(), ISBLANK([2nd Completed]1))), " 2.Over", " 2.Not") + IF(AND(AND(ISDATE([3rd Date]1), [3rd Date]1 < TODAY(), ISBLANK([3rd Completed]1))), " 3.Over", " 3.Not") + IF(AND(AND(ISDATE([4th Date]1), [4th Date]1 < TODAY(), ISBLANK([4th Completed]1))), " 4.Over", " 4.Not") + IF(AND(AND(ISDATE([5th Date]1), [5th Date]1 < TODAY(), ISBLANK([5th Completed]1))), " 5.Over", " 5.Not") + IF(AND(AND(ISDATE([6th Date]1), [6th Date]1 < TODAY(), ISBLANK([6th Completed]1))), " 6.Over", " 6.Not") + IF(AND(AND(ISDATE([7th Date]1), [7th Date]1 < TODAY(), ISBLANK([7th Completed]1))), " 7.Over", " 7.Not") + IF(AND(AND(ISDATE([8th Date]1), [8th Date]1 < TODAY(), ISBLANK([8th Completed]1))), " 8.Over", " 8.Not") + IF(AND(AND(ISDATE([9th Date]1), [9th Date]1 < TODAY(), ISBLANK([9th Completed]1))), " 9.Over", " 9.Not") + IF(AND(AND(ISDATE([10th Date]1), [10th Date]1 < TODAY(), ISBLANK([10th Completed]1))), " 10.Over", " 10.Not") + IF(AND(AND(ISDATE([11th Date]1), [11th Date]1 < TODAY(), ISBLANK([11th Completed]1))), " 11.Over", " 11.Not") + IF(AND(AND(ISDATE([12th Date]1), [12th Date]1 < TODAY(), ISBLANK([12th Completed]1))), " 12.Over", " 12.Not")

Hoping someone can help come up with a better solution