Conditional Formating based on calculations in other cells Help please

Good evening all i have a small issue with conditional formatting that im hoping some gurus can help with.

So in this work sheet i have a cell that i enter a date for a Particular item. i enter various info up to the Category point
Within the category is a formula/formatting that performs a function of changing the color of the cell from green(minor) orange(Major) and red(critical) if those words are entered. if nothing is in the cell it stays blank.

In the number of days allocated field it references the KEY at the top which can be changed if i ever need to the number in the cell that corresponds to minor,major,critical etc.

This is where my problem starts.

P12 is a calculation result based on the date Entered D12 + the number in O12 and this result i would like to conditonaly format based on certain out comes.

If Cell O12 = 7 then i want P12 to Show a color background
1) the date in P12 is between 1 and 4 days of the actual todays date Green (which could be hidden in a cell somewhere?)
2) the date in P12 is between 5 and 7 days of the actual todays date orange
3 the date in P12 is between 7 Or More days of the actual todays date REd
Providing that the in Cell Q12 = Open if it = Closed or " Blank" then be blank grey.

In conjunction to the above i want it to also consider if there is 14 or 21 in the O12 cell

If Cell O12 = 14 then i want P12 to same colors but green from day 1 to 9, orange 10 to 14 and red 15 + except if Q12 = Closed etc

If Cell O12 = 21 then i want P12 to same colors but green from day 1 to 15, orange 16 to 21 and red 21 + except if Q12 = Closed etc

Hope this makes sens here??
The net result is if there is nothing entered in D12 all entries will be Clear with a white background
Then with a combination of a date in D12 + Category being defined as a minor, major,critical it will the auto fill O12 to determine number of days which is used to calculate the time frame to which it must be completed.

Based on that the background formatting will change in color depending on which category is entered which changes the time scale in numbers of days thus changing When each back ground color is shown...

Im sure there must be an easy way to do this but i cant get my head around it and tried to create 9 different conditional formats for the cell but never got it to work.

Please help

Regards
Dave

I thought of an easier way to explain:

How do i turn this below code in to code that checks for a result In O12 is = 7 then performs the formating? i could then in theory create several versions for checking O12 having 7 or 14 or 21 and format with what i need

=$T$4-2>=$P$12

Thank you for reply, it has kind of worked but did not hehe

so in one cell i define if its a minor, major or critical. Based on that O12 references a number 7, 14, 21 As you can see if you type in minor or major or critical it changes colour and the number in O12
changes to reflect this. (See new file)

P12 is the last day for completion so from day 0 to 3 into 7 should be green background and from day 4 into 7 should be orange background - 8 days and onwards should be red unless the word "closed" exists in Cell Q12 in which case it can be kinda greyed out.....


If the category for example reads as major then in Cell O12 would be 14 in this case i split the 14 days up like above but widening the range slightly. 0-10 green, 11-14 orange, 15+ red unless "Closed" = grey

If the category for example reads as minor then in Cell O12 would be 21 in this case i split the 21 days up like above but widening the range slightly. 0-15 green, 16-21 orange, 22+ red unless "Closed" = grey

In cell the conditional format might require maybe 9 total conditional format layers unless theres a formula way to do it???

The way file is now you can change the date in D12 from 20th to 21st and using your suggestion will change P12 from red to orange if you change the date by 1 day. but i cant get the formula right to do the Green Background.. Once that is done can think about adding the other 2 Category types for 14 days and 21 if that makes sense?

As it is now you can see bottom part shows orange but should be green P16 etc
Corrective Action Log Draft2.xlsx