Formula Assistance Crispin

New Contributor

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.




5 Replies
best response confirmed by allyreckerman (Microsoft)


See the attached version.

@Hans Vogelaar 

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





@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!!










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

Thank you @Hans Vogelaar!