SOLVED

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.

 

 Thanks!


C

5 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Crispin2410 

See the attached version.

@Hans Vogelaar 

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

 

v/r

 

Crispin

@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

 

 

 

 

@Crispin2410 

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

Thank you @Hans Vogelaar!