Conditional Formatting and Filter

  • Thread starter Thread starter LBerry
  • Start date Start date
L

LBerry

I only know basic functions but what I'd like to do is have a column with the
date I need to have things done by, and then a column next to it with the
remaining days I have left to complete that task. I would like to have the
"remaining days" column where it is color coordinated so that it shows up
green when i have three days or more, yellow at two days, and red at 1 day or
less. I tried using the =now() function and then deleting that from the date
but it doesn't really show up right and the =networkdays() only shows up
amount of work days. And the real thing I can't do though is to filter the
days. I would like to be able to add something new that is due sooner and be
able to refilter the column so that most important (red 1 days) are at the
top and so on. I can't figure it out and need help bad. Thanks
 
Let's deal with one at a time.

1. If a1 has a completion date, the days remaining to completion is simply
=today()-a1. Format as general.
2. Now you should have a column of 'days to completion' (with values like
1,2,3, etc.)
3. Unless you have XL2007, you're limited to 3 conditional formats. So
decide which 3 are the most important (ie 1 day left, 2 to 5 days left, more
than 5 days left).
4. Highligt the first cell in the column. Use Format>Conditional Formatting.
Enter the three format conditions you want.
5. Copy the formats down the column (right-drag the fill button, release it
and choose Copy formatting only...)
6. Finally, to get the "most important" task on top, you will need to sort
the table by 'days to completion'

Regards,
Fred.
 
Thanks, that looks awesome, so much help. Although I have a follow up, so
right now I have the days left column on a color scale from green to red and
black when it is 0 days or negative days. The problem is that some stuff I
have on the list doesn't have a defined due date til later on so the "days
left" column is blank, but thats causing it to show up as black because I
guess the value is 0. I was just wondering if there is some way to keep it
where 0 and negative days are black and the blanks cells are not shaded in.
Thanks, the last answer was so awesome. I normally hate help bulletin boards
but this was great.
 
If you are currently looking at your days left column with a condition of
Cell Value Is/ less than or equal to/ 1, try changing the condition to
something like:
Formula Is/ =AND(A1<>"",A1-TODAY()<=1)
 
Okay, that was a little over my head, I'm not sure where to put that in, in
the conditional formatting section under the formula section? And when I
tried it, it just made all the grids turn that color instead of just adding
another condition. I'm sure the answer probably does answer it, its just
over my head. Also is there anyway to add a condition where if i typed in
"done" or "complete" or something, that the green check mark (from the icon
sets) or a thunderbolt (TCB) would show up in the square? I know I am all
over the place with this, I just like excel but have very limited
capabilities on it and never had anyone to ask, so seriously thanks so much.
 
Put that in place of the Conditional Formatting condition that is turning
all your blank cells black.
 
Back
Top