setting up to-do list with conditional formatting and dates

[SHAWKINS01]

I am having problems setting up the conditional formatting for a "To-Do" type of list. I want to assign tasks to be completed on Excel 2010 spread sheet, set dates and have it change color depending on whether it is on schedule or not. Can you help me with this?

Task is set up in B1
It is assigned to a person in B2
Expected completion date in B3
B4 is where the date of completion is entered.

I want B3 to be Green until it gets within 1 week of the Expected completion date
If it is not completed within 1 week of the expected completion date then have it turn Yellow
If it is not completed by the completion date then turn Red.

I have tried multiple ways to get this done but I was hoping that there was a way to have it check B3 if a completed date is entered then turn green or red depending on if the task was completed before the expected due date, or red if not completed by the due date. If this is too tricky, then I can axe it, the first part is the most important.

Please let me know if any additional data is needed.

Thank you,
Shawn

[sandy666]

any sample file (excel, not picture or text) with what you have and what you expect?

[SHAWKINS01]

Sorry, there were a few updates to the spreadsheet.

Task is set up in B3
It is assigned to a person in C3
Expected completion date in E3
F4 is where the date of completion is entered.

I want E3 to be Green until it gets within 1 week of the Expected completion date
If it is not completed within 1 week of the expected completion date then have it turn Yellow
If it is not completed by the completion date then turn Red.

Open Action Items 3.xlsx

[sandy666]

is this (that?... whatever ) what you want?

Open Action Items 3aaa_sandy.xlsx

[SHAWKINS01]

Close, I am trying to track if the completion dates (F3 - F8) are before or after the the Due dates (E3 - E8). Looks like you have it set up the other way around.

From your version:
E3 and F3 are = So i would like it green.
F4 is greater than the due date of E4 so E4 would be red. (Past Due)
F5 is greater than the due date of E5 so E5 would be red. (Past Due)
F6 is greater than the due date of E6 so E6 would be red. (Past Due)


What I am looking for:
Due date is 01-March-2015 and it has not been completed then Red since it is past the Due date.
Due date is 30-March-2015 and it has not been completed then Yellow since it still has a week to complete.
Due date is 01-May-2015 and it has been completed then Green

Does that help?

[sandy666]

maybe
Open Action Items 3bbb_sandy.xlsx

[SHAWKINS01]

Closer, I flipped the greater than and Less than symbols to get it to work.
E3 shows 12-May-2015 and F3 shows 20-MAY-2015, Since it would be past due I would like it red. You had the formula as =F3-E3>7 so I flipped it to =F3-E3<7 and it went red for me.

I think the only part not working that I would like to have is if F3-F8 are blank and E3-E8 are within 7 days or past due it turns Yellow and then Red. Right now it doesnt look like it is calculating the date in correlation to the current date the spreadsheet is being looked at. Is that something that can be done?

[SHAWKINS01]

Actually the Yellow option you have =F3-E3<=7 is conflicting with Red =F3-E3>0 and Green =F3-E3<7
I only want it yellow if it has not been completed and the Due Date is within 7 days. Is there a formula to say if F3 =0 or is blank and E3 is less then or = to today then turn yellow?

[sandy666]

[...deleted]

hope it is what you want

Open Action Items 3ccc_sandy.xlsx

[SHAWKINS01]

That is what I needed, but management has scrapped it all and said they want it more simplified. I dont how to wright it in formula but here is what they are wanting:


If E3 is less than or = to Today and F3 is blank then fill Red.
If E3 is 7 days greater than Today and F3 is empty then fill Yellow
If F3 is dated then fill Green

So now they only want to see red if Column F is not dated and Column E is either due today or past due.
They want yellow to prompt them to act if the due date is within the next 7 days and the task has not been completed.
And Green once the task is completed regardless if it is past the due date.
If E3 is = to Today and F3 is blank then fill Red.

Sorry for all the revisions, you have been a great help with all of this Sandy!

[sandy666]

try

1. =AND(F3=0,E3>=TODAY()) [red]
2. =AND(F3=0,E3+7>TODAY()) [yellow]
3. =E3<=F3 [green]

[SHAWKINS01]

That worked, i am good now.

Thanks again for all your help.

[sandy666]

I am glad

Mark thread as solved