Formula Assistance Crispin

[Crispin2410]

I need assistance with multiple cells/formulas.

1) In the attached example C4 is the date required for the contractor to submit the CDRL. D4 is the actual date the CDRL was received.

     a) I would like C4 to turn yellow when it is 10 days from being due. Then turn red if it exceeds the due date. Once a date in D4 is entered regardless of whether its late or not, cell C4 turns green. 

     b) Once a date is entered in D4, cell E4 changes from "Pending" to "Completed"

2) F4 is a 15 day countdown from when a date is entered in D4. At 5 days it turns yellow. If the countdown exceeds is turns from + to - number and changes from yellow to red. Once a date in G4 is entered then the countdown changes from red to green, and the countdown stops at the last number before a date was entered in g4.

 Thanks!

C

[#2 · Accepted solution]

@Crispin2410 

See the attached version.

[#3]

@Hans Vogelaar 

YES! That is exactly what I am looking for. I hope it was not too difficult. 

v/r

Crispin

[#4]

@Hans Vogelaar 

Thanks again for your assistance. I have two more columns to address; perhaps you could tackle this challenge as well.

1) In column I, row 4 (I4), I need a countdown that starts according to the date listed in column G, row 4 (G4), "IF" the word "Rejected" is in column H, row 4 (H4). If the word "Accepted" is listed no further action is required. 

2) In column J, row 4 once a date is listed the countdown stops in I4 and the cell turns green, regardless if the countdown exceeded the 10 day marker. 

Any assistance is very much appreciated!!

Regards,

Crispin

[#5]

@Crispin2410 

It's the same idea as for F4 - see the attached workbook.

[#6]

Thank you @Hans Vogelaar!