Hi, I have a work diary where I input my activities then due dates, status, comp

Question

Hi,

I have a work diary where I input my activities then due dates, status, completion date.

Column A = Activities

Column B = Due Date

Column C = Status - I have conditional formatting here, On Target (BLUE), Overdue (RED) and Completed (GREEN). It automatically goes to completed once Column D has a completion date.

Column D = Completion Date - This is formatted to go GREEN for dates completed on or before the due date and RED when it is overdue.

I would like my due date column to change and have the following conditional formatting:

GREEN - 21 or more days until due date AND once a task has been completed.

ORANGE - 14 days until due datee.

RED - 5 days until due datee.

I am thinking it is an IF function that I wouldd need! to use but I can't get it rightt.

Explanation / Answer

Hi,

If I've understood your question correctly, you want your B Column (Due Date) to change colour based on three criteria. My suggestion is:

- GREEN - 21 or more days until due date AND once a task has been completed.

Conditional formatting with a formula. For 21 or more days until due date use:

=$B2+21<=TODAY()

for once task has completed use:

=$D3<>""

- ORANGE - 14 days until due date.

=$B2+14=TODAY()

- RED - 5 days until due date.

=$B2+5=TODAY()

Note that you have to type these formulas in the formula box for conditional formatting.

You can select B2 to Bn (where n is the ending row of your table) and then select conditional formatting--> new rule--> use a formula to determine which cells to format; and then type above formulas. It will work for all rows.

Each of above formulas should be added as a new conditinal formatting.

Hope this helps.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.