Conditional formatting dates

[Closed]
Report
Posts
1
Registration date
Monday August 24, 2009
Status
Member
Last seen
August 25, 2009
-
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
-
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

1 reply

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
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.