Conditional formatting cells containing certain values

[patwhit14]

Hi all,

Let me start by saying I am very much an amatuer at Excel, so I apologise if this is very straight forward!

I am having a bit of trouble working out how to use conditional formatting properly. Basically what I am trying to achieve is to have certain cells change colours based on their values (which is in days on my spreadsheet).

I service equipment on a regular basis (minor service 3 times per year and major service once a year). When a certain machine is due within 14 days I want text to turn red and when due between 15-30 days I would like the text to turn orange. At all other times I would like the text to be green.

The sheet contains 4 columns which these would apply to; "Q1 minor", "Q2 minor", "Q3 minor" and "Major", each of these would countdown to a date each year.

The issue that I am having is that my cells contain words as well as numbers, for example the cells contain "8 days" rather than just the number "8".

If possible I would also like to be able to check off these services once they are complete which would reset the countdown.

Any help would be hugely appreciated.

Thanks,

Pat

 

[Special-K99]

Can you supply a small sample of the data range, e.g. 8 days, 2 days, 20 days etc so we can see what range of data we have to deal with or are they all specifically "n days"?

 

[patwhit14]

Can you supply a small sample of the data range, e.g. 8 days, 2 days, 20 days etc so we can see what range of data we have to deal with or are they all specifically "n days"?

Each column will have a countdown to a date in the year, so each column will be counting down from 365. The values which are 31-365 days will show in green text, the values which are 15-30 days will be orange and the values which are 1-14 days will be red.

 

[Special-K99]

So, same question.
Are they ALL specifically "n days"?

 

[patwhit14]

So, same question.
Are they ALL specifically "n days"?

Sorry, I misunderstood. Yep, they are all specifically a number of days until each service is due.

 

[patwhit14]

This is the very early stages of the sheet and is more or less all I need. Please note I have not entered any dates etc. yet so all values are the same until this is done.

 

[Special-K99]

Try

Select A1

Conditional Formatting
New Rule
Use a formula to determine...

=(LEFT(A1,FIND(" ",A1)-1)+0<=14)
format as red

=(LEFT(A1,FIND(" ",A1)-1)+0<=31)
format as orange

=(LEFT(A1,FIND(" ",A1)-1)+0>31)
format as green

Use Format painter (paintbrush icon) to copy to other cells

UPDATE: I'm at work at present and unable to see that picture.

 

[patwhit14]

Try

Select A1

Conditional Formatting
New Rule
Use a formula to determine...

=(LEFT(A1,FIND(" ",A1)-1)+0<=14)
format as red

=(LEFT(A1,FIND(" ",A1)-1)+0<=31)
format as orange

=(LEFT(A1,FIND(" ",A1)-1)+0>31)
format as green

Use Format painter (paintbrush icon) to copy to other cells

UPDATE: I'm at work at present and unable to see that picture.

That did not seem to work, it might be important to add that I have already played around with having cells countdown to a date so I have already used the formula below -

=DATEVALUE("1-October")-TODAY()&" days"

I added your formula above and used the format painter to all these cells which did not change colour.

 

[patwhit14]

Try

Select A1

Conditional Formatting
New Rule
Use a formula to determine...

=(LEFT(A1,FIND(" ",A1)-1)+0<=14)
format as red

=(LEFT(A1,FIND(" ",A1)-1)+0<=31)
format as orange

=(LEFT(A1,FIND(" ",A1)-1)+0>31)
format as green

Use Format painter (paintbrush icon) to copy to other cells

UPDATE: I'm at work at present and unable to see that picture.

OK, so I've gone back and had another play and have had some luck this time, not sure what I was doing wrong last night (I was very tired!).

It seems to be working now, however I am getting some errors whenever I manually change the dates which each cell is counting down to?

I really appreciate all the help so far.

Pat