IF Formula for task row containing mini tasks in columns in order from left to right

I have a document that is setup per client as the primary column. Each client row represents a task to complete audited financials each year for each client. Each column from left to right represents a sub-task during the audit process outlined below. I have setup each column to use conditional formatting to change the colour of the cell in that row to red if the sub-task has not been completed per the logical steps outlined below. I am busy setting up workflows to automatically alert the managers and request input by the responsible people for each sub-task if overdue. I would like to create reports and dashboards on all clients (rows) that have a sub-task overdue somewhere in each row so that we can have a live global overview for Director and management levels.

I was told that you can't create reports or health status based on conditional formatting in a row so I thus need to create a very complicated IF formula to change the health status of that row to red if any sub-task is overdue so I can report on health status OR I need to create a very complicated report builder based on every overdue sub-task in each row. Either way is fine as long as I can get the report and dashboard going on all the sub-tasks and each row main task.

I have outlined each sub-task of each row below and also provided a snapshot of what the row looks like. I would really appreciate it if someone could please guide me to the correct way of reporting this.

Sub-task 1: Submission Deadline - Bookkeeper must submit the audit pack to the auditor by the deadline date. Submission done checkbox must be ticked if submission deadline in past or task is overdue and red.

Sub-task 2: 1st queries received - auditor has 7 days from submission deadline to send 1st batch of queries if any. Bookkeeper would enter a date for when the 1st queries are received. If the 1st query deadline is in the past and no date is entered in the 1st queries received cell, then task is overdue and red.

Sub-task 3: 1st queries responded - bookkeeper has 2 days to respond to those queries once received. If 2 days have passed from the date entered in 1st queries received and the 1st queries checkbox is unticked, then task is overdue and red.

Sub-task 4: Final queries received - auditor has 12 days from submission deadline to submit final queries if any. If final queries received cell is empty and final queries deadline is in the past, then the task is overdue and red.

Sub-task 5: Final queries responded - bookkeeper has 2 days to respond to final queries. If 2 days have passed from date in final queries received cell and final queries responded checkbox is unticked, then task is overdue and red.

Sub-task 6: Draft AFS received - auditor has until the end of the same month of submission deadline to complete the audit and submit the draft financials to us. If the draft AFS deadline date is in the past and the drafts received cell is empty, then the task is overdue and red.

Sub-task 7: Team meeting held - team needs to meet within 2 days of receiving draft to discuss. If 2 days from drafts received date has passed and the teams meeting held checkbox is unticked, then the task is overdue and red.

Sub-task 8: Committee queries resolved - If committee queries = yes and committee queries resolved checkbox is unticked then task is overdue and red.

Sub-task 9: AFS Signed - the team has until the deadline noted in AFS Signing Deadline to get the financials signed by the client. If AFS Signing Deadline is in the past and the AFS signed checkbox is unticked, then task is overdue and red.

Sub-task 10: Signed AFS sent to auditor - if the AFS signed checkbox is ticked and the Signed AFS sent to auditor is unchecked then task is overdue and red.

Sub-task 11: Post audit adjustments done - if the AFS signed checkbox is ticked and the post audit adjustments done checkbox is unticked then the task is overdue and red.