Conditional formatting

[Josh]

OK this is going to be hard to explain.

OK I want to set conditional formatting for a set of numbers. The cells i
am trying to format says anything from 365 - 0 DAYS.
I am trying to get the cell to
turn green when it is above 30 Days
Yellow when it is between 31 and 15
red when it is below 16.

I had it working before I added the work DAYS to the cell. Now I can not
figure out how to make that conditional formatting work when the word "DAYS"
is in the cell.

 

[Pete_UK]

Presumably you had a formula which worked out days (as a number)
before, and you have now changed it to return &" DAYS" at the end, and
so it is now text. You can get the word DAYS to show in the cell
through formatting, so that your previous conditional formatting will
still work on the underlying numbers - use a custom format on the cell
of:

0" days"

Hope this helps.

Pete

 

[~L]

Where before you might have had =A1>30 as the condition for green, now use
=--(IF(LEN(A1)>7,(LEFT(A1,3),LEFT(A1,2)))>30

Same for the others, if you had them working with just a number now use
=--(IF(LEN(A1)>7,(LEFT(A1,3),LEFT(A1,2))) in place of the cell reference.

 

[Shane Devenshire]

Hi,

First there is an overlap of conditions:
Above 30 is green
31 is yellow?
Similar problem with between 15-31
and below 16.

No formulas necessary!

Let's suppose you really want this

1. Select the range and choose Format, Conditional Formatting
2. From the second drops down choose greater than and in the 3rd box enter 30
3. Click Format and on the Patterns tab pick a color (green), click OK once.
4. Click Add and from the second drop down for the 2nd condition choose
greater than and in the 3rd box enter 15.
5. Repeat step 3 picking Yellow
6. Click Add and from the second drop down for the 2nd condition choose less
than or equal to and in the 3rd box enter 15.
7. Repeat step 3 picking Red.

 

[Josh]

I did it close to that way initially and it worked but then I changed the
cells formatting to add the word DAYS. This is how i did it initially

1st condition - Select the range and choose Format, Conditional Formatting
From the second drop down choose greater than and in the 3rd box enter 30(31
and above)
Click Format and on the Patterns tab pick a color (green), click OK once.

2nd condition - Click Add and from the second drop down for the choose less
than and in the 3rd box enter 16(15 and below) than pick (red)

3rd condition - Click Add and from the second drop down for the choose
between to and in the 3rd box enter 31 and 15. (30-16)
than I pick yellow.

Now that the word DAYS is in the cell the formatting will not work.

 

[Josh]

this is my formatting for the cells I am tring to do the conditional
formatting on

=E105+365-TODAY()&" DAYS"

 

[Pete_UK]

Change the formula back to:

=E105+365-TODAY()

and use Format | Cells | Number tab | Custom (at bottom of list). In
the Type: box enter:

0"DAYS"

(exactly as it appears above) and click OK, and then you will not have
to include the word DAYS in your formula. You previous Conditional
Formatting (based on numeric values) should now work, as you do not
have the word DAYS actually in the cell.

Hope this helps.

Pete

 

[Josh]

YESSS!!!!!!!! You should be awarded some kind of medal or something that was
awesomely easy........ Thanks a lot

 

[Pete_UK]

You're welcome, Josh - thanks for feeding back.

No medals here, I'm afraid !!

Pete

 

[Shane Devenshire]

So in other words the supplied answer worked and so you changed the
question? Maybe in the future you could just ask the you want answered. If
people spend their time solving your problems but you just keep changing the
problem, you are wasting their time.


Cheers,
Shane Devenshire