Conditional formatting dates
Closed
laddy1975
Posts
1
Registration date
Monday August 24, 2009
Status
Member
Last seen
August 25, 2009
-
Aug 25, 2009 at 10:15 AM
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 - Aug 25, 2009 at 04:27 PM
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 - Aug 25, 2009 at 04:27 PM
Related:
- Conditional formatting dates
- How to clear formatting in excel - Guide
- Excel conditional formatting if another cell contains specific text - Excel Forum
- Excel formula to change cell color without conditional formatting - Excel Forum
- To change conditional formatting that applies a red fill color to one that applies a green fill color, which of the following can you do? - Excel Forum
- Phone formatting software for pc - Download - File management
1 response
sharpman
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
183
Aug 25, 2009 at 04:27 PM
Aug 25, 2009 at 04:27 PM
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.
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.
