• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formatting to give a traffic light effect when a date has expired

A

Anthony Pinnegar

Member
Any suggestions on how I would set up a traffic light effect for dates on a spreadsheet. Green would have been completed, orange would be outstanding and red would be overdue.

For example, a review would need to be completed within a fortnight of a specific date. If it is within this period of time the cell will be highlighted orange/amber (the cell will be blank). If this two week period has expired the cell would be coloured red (the cell is still blank), and when the the review has been completed a date will be added to the cell and it will be coloured green.

Presumably it will be a type of conditional formatting.

Thanks in advance,
Anthony
 
Anthony

Can you post the file you want and highlight where you want it applied
 
Hi Hui, please find attached example spreadsheet. If you have any questions do come back to me.

Anthony
 

Attachments

  • Log1.xlsx
    14.2 KB · Views: 5
Is this what your after, as the exact requirements aren't very clear

upload_2015-3-4_22-7-36.png
 

Attachments

  • Log1.xlsx
    14.4 KB · Views: 4
Hi Hui, if you change the number in cell G6 to a date (x weeks (2, 2, 4, 1, 2 or 3 weeks) after they started the job) and change the whole cell to the colour of the dot that is exactly what I am after.

The cells will automatically populate in columns G, H, I, J, K and L when a start date has been entered, and as they are completed or fall overdue they will change from Amber, to either Red or Green.

I hope this helps.

Anthony
 
Do you mean like this?
upload_2015-3-4_22-42-43.png but with colored cells

How do we know what Date it is compared against?

EG: If I assume today 4/3/15 then all the cells are before that and so what tells me what is completed etc ?
 
All dates in Column G would be 2 weeks after the start date, Column H would also be 2 weeks after the start date, and so on with 4 weeks, 1 week, 2 weeks and 3 weeks in columns I, J, K and L.
My preference would be for the cells on each line to be shaded Amber until after the due date, which is based on the entered start date for each employee, when it will change to Red if it hasn't been completed, with the cell only turning Green when an actual date has been entered, to show that the scenario has been completed.

Not sure if this makes sense.
 
You must log in or register to reply here.
Back
Top