SOLVED

New Contributor

# Formula Assistance Crispin

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

# Re: Formula Assistance Crispin

See the attached version.

# Re: Formula Assistance Crispin

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

v/r

Crispin

# Re: Formula Assistance Crispin

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

# Re: Formula Assistance Crispin

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

# Re: Formula Assistance Crispin

Thank you @Hans Vogelaar!