Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 7
There are 1 users currently browsing forums.
|
 |
07-02-2009, 09:12 AM
|
|
Registered User
|
|
Join Date: 02 Jul 2009
Location: Cleveland, Ohio
MS Office Version:Excel 2007
Posts: 4
|
|
Formatting a Cell based on Date in another
Please Register to Remove these Ads
I am currently working on a spreadsheet in Excel 07, I need to color code one cell based on the date in another cell.
If the date in cell (A1) is greater than 7 days old, I need cell (D1) to turn Yellow, If greater than 10 days old, need cell (D1) to turn Red.
Any help you can provide is greatly appreciated.
Thanks,
~T
Last edited by RunNWild; 07-02-2009 at 02:01 PM.
|
07-02-2009, 09:18 AM
|
 |
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,532
|
|
|
Re: Formatting a Cell based on Date in another
This is conditional formatting (you'll have to locate the menus in 2007).
Highlight D1 and activate the conditional formatting controls:
Condition1: Formula Is: =TODAY()-10>A1
Format...pattern...red
Condition1: Formula Is: =TODAY()-7>A1
Format...pattern...yellow
__________________
If you've been given good help, use the  icon to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-02-2009, 11:05 AM
|
|
Registered User
|
|
Join Date: 02 Jul 2009
Location: Cleveland, Ohio
MS Office Version:Excel 2007
Posts: 4
|
|
|
Re: Formatting a Cell based on Date in another
This is what is happening now:
I created the following: =B6+7>TODAY()
This is turning the cell red if the date in B6 is over 7 days from todays date, but I cannot figure out how to get the rows that have no date listed in the first cell not to turn red without entering a date. I only want this to execute if there is a date value in the first cell.
I should be able to highlight the cell with the formula in and copy it down the column to have it active on as many rows as I wish, correct??
|
07-02-2009, 11:15 AM
|
|
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,532
|
|
|
Re: Formatting a Cell based on Date in another
Try this:
=AND(B6>0,B6+7>TODAY())
__________________
If you've been given good help, use the icon to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-02-2009, 11:25 AM
|
|
Registered User
|
|
Join Date: 02 Jul 2009
Location: Cleveland, Ohio
MS Office Version:Excel 2007
Posts: 4
|
|
|
Re: Formatting a Cell based on Date in another
Same result, in rows with no date, the cell in the column with formula still changes color.
|
07-02-2009, 11:58 AM
|
|
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,532
|
|
|
Re: Formatting a Cell based on Date in another
You can experiment with these formulas in a regular cell without having to keep going into the CF windows. In a cell, enter the CF formulas and play around. If the formula returns TRUE, it will trigger the color in CF, if FALSE it will not.
So, play with each piece of the formula, get each part to confirm TRUE/FALSE properly alone before you nest them together into an AND() statement. This is how I keep my sanity dealing with complex CF formulas.
Once it is working in a cell, then put it into the CF matrix.
__________________
If you've been given good help, use the icon to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-02-2009, 12:17 PM
|
|
Registered User
|
|
Join Date: 02 Jul 2009
Location: Cleveland, Ohio
MS Office Version:Excel 2007
Posts: 4
|
|
|
Re: Formatting a Cell based on Date in another
I finally got it to work correctly. Thanks for the help JBeaucaire. Here is what I ended up using.
=IF(B6=0," ",B6+7>TODAY())
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
