Conditional formatting for date that has past based on cell containing a formula

[michelleauorahq]

I have an excel spreadsheet with 2 sheets that are connected by formulas.

Sheet one has a date that is the base date for the calculation of project timelines. On that sheet I am calculating a due date based on weeks/days prior to the base date.
C3 = base date being 01/07/2024
C15 = how many weeks prior to or after the base date the task is due = 45 weeks
D15 = formula =$C$3 - C15*7
This gives me a due date based on the above of 21/08/2023.

Sheet 2 I have a column for the status to be updated in column A and then due date copying it across from sheet 1 (=sheet1name!D15) in column B.

I want to be able to conditionally format preferably column B in sheet 2 or column D in sheet 1 to be that:
if the status in column A is either blank or has any other words in it other than "complete" (ie in progress ) AND the due date is in the past based on today's date, then it would highlight to be the fill/colour I choose (ie likely red).

I don't want to manually enter the dates as I am setting it as a template and the dates will change depending on the base date for each project. Replacing the formula with a static date isn't an option.

I tried a couple of options based on what I found online but it didn't seem to work.

Screenshots provided of each sheet and the formulas being used.

[#2 · Accepted solution]

@michelleauorahq 

On Sheet2, select B8:B100 or however far down you want.

B2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($A8<>"Complete", $B8<>"", $B8<TODAY())

Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.

[#3]

Thank you so much. That worked! I tried a few different options and couldn't get it to worth. You're a life saver!