Calculate Complete by Date based on a Schedule

I cannot seem to figure out how to write a formula in column J to calculate the complete by date.

Context:
The Complete by date should be calculated by using the Category & the Due Date from Table 2 & matching that up with the Check Run schedule in Table 1. Basically, the Complete by date should be 1 business day (M-F) before the next check run (check run is indicated by an X and some categories have multiple check runs in the same week). Special Note: If the Due Date is on the same day as a check run, then the previous check run date will need to be referenced to calculate the Complete by date. The reasoning for this is that check runs are done in the morning so completing the task in the afternoon means the check would be late as it would miss the same day check run.

Column J is what I need a formula for, but it currently indicates what I would expect the formula to return based on the Category and Due Date. I added a pic of a calendar for easier reference. I will give a few examples below that further explain the formula logic I am looking for.

Complete By Date Based on CheckRun Schedule.png

Example 1 (Cell J3):
Due date = 6/8/23. In order to hit this goal, the complete date needs to be 1 business day before the closest check run. Since the closest check run is on Monday (6/5), the complete date needs to be 1 business day prior which is where 6/2/23 comes from.

Example 2 (Cell J3):
This example utilizes the special note above. Due Date = 6/12/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Monday (6/5/23), the complete date needs to be 1 business day prior which is where 6/2/23 comes from.

Example 3 (Cell J10):
This example utilizes the special note above. Due Date = 6/6/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Thursday (6/1/23), the complete date needs to be 1 business day prior which is where 5/31/23 comes from.

Example 4 (Cell J16):
This example utilizes the special note above. Due Date = 6/9/23. In order to hit this goal, the complete date needs to be 1 business day before the closest PREVIOUS check run. Since the closest PREVIOUS check run is on Thursday (6/8/23), the complete date needs to be 1 business day prior which is where 6/7/23 comes from.

HansDouwe,
Both of these solutions are exactly what I was looking for....thank you so much!!

A new scenario has just came up regarding this same topic. I've been using the solution above flawlessly for the last couple of months. The data from Table 2 had to be exported into Excel on a daily basis which is why I was originally looking for a solution in an Excel formula. Recently, I have been able to get the data from Table 2 via a database connection in Power Query. With that said, I'm now looking to get the exact same solution, but in Power Query instead of an Excel formula.

I've taken a few steps to doing this in Power Query, but I'm just not quite there yet. I may be completely off track in my method. The circled column (Custom.1 from Power Query) needs to match the circled column (Column J from Excel).

Screenshot 2023-08-24 124132.png

Here's the advanced editor code:

Please Login or Register  to view this content.