Conditional formatting dates [Closed]

laddy1975 1 Posts Monday August 24, 2009Registration date August 25, 2009 Last seen - Aug 25, 2009 at 10:15 AM - Latest reply: sharpman 1023 Posts Saturday May 23, 2009Registration dateContributorStatus October 20, 2010 Last seen
- Aug 25, 2009 at 04:27 PM
Hello All,

Hope all is well, I'm new to this site, and was wondering if anyone can help with the following conditional formatting:

In cell A1 I have a date & B1 I have a date, if the date in B1 is less than 3 weeks of the date in A1, then I would like B1 to change colour

Many thanks
See more 

1 reply

sharpman 1023 Posts Saturday May 23, 2009Registration dateContributorStatus October 20, 2010 Last seen - Aug 25, 2009 at 04:27 PM
0
Helpful
This is for office 2007.
if you have a different one let me know. i'll try to adapt it for you.

ok,i have a solution for you. It may not be the best or tidiest, but it works.

here goes

this is the formula that you will be using

=INT((B1-A1)/7) this calculates the number of weeks between 2 dates.

create your column of dates in A, and B in column C put the above formula.

so you have
A B C
Date Date =INT((B2-A2)/7)

with me so far.

high light the first cell in B1 and select conditional formatting, /highlight cells with rules,/ more rules .....
select the bottom option
'Use a formula to determine which cells to format'

click on the little spreadsheet icon, click on cell C1.
in the new formatting rule window will appear the following =$C$1
type > 3 after the =$C$1.

so it should look like this - =$C$1 < 3

this is basicaly saying format this cell if cell C1 is less than 3 (in this case less than 3 weeks)

ok, now click the format ..... button (above the cancel button)

click on the fill tab, select the colour that you require it to change to. bearing in mind, depending on what colour you use you may have to change the text colour as well so it stands out. (if for instance you change the fill colour to red or black, I would change the font to white so that it can be seen)

now test the results by adding several dates in both columns. some 3 weeks or more and some less than 3 weeks.

(if you want it to be 3weeks or less then in the formula above change the < to <= this means eaqual to or less than, so it would show all dates 3 weeks and less)

i'm assuming you know how to copy the formulas to the following cells.

You'll have to do the conditional formatting for each cell that you need.
i'm afraid a fault in 2007 won't allow to copy conditional formatting allowing for the diferent cell references. its ok if you want to use the same cell reference.


ok, if you are happy with everything and it works to what you want then to tidy up you just need to hide column C.


Or you could put the contents of column c in any column towards the end of your spreadsheet, just adapt the formula with the correct cell number.


hope you got all that.