Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formating a row based on one cell's date
Hello,
I'm working on a spreadsheet that has columns A - G. Column A has the dates. I would like to create a format that will highlight the entire row if column A has a date within the past 7 days. In my example for of the rows should be highlighted. I was able to find formulas that only worked on one cell. Can someone help me with this? PLEASE & THANK YOU! **Example** 7/17/12 DEA MA ACCT CONT Closed 7/13/12 HHI MA ACCT CONT Closed 7/10/12 MMG MA ACCT CONT Closed 7/16/12 ABC P ACCT CONT Closed 7/13/12 TAL W ACCT CONT Closed 7/20/12 HOK P ACCT CONT Closed |
#2
|
|||
|
|||
Quote:
Is this what you mean? You use conditional formatting and lock the column using $ in the formula. Have a look at the conditional formatting rule to see what I mean. |
#3
|
|||
|
|||
Thanks for the response but I'm looking for one rule instead of two.
|
#4
|
|||
|
|||
Quote:
You could use =$A1=TODAY()-6 but that will also highlight rows with dates in the future. Last edited by Spencer101 : July 17th 12 at 06:39 PM Reason: Formal amended to -6 rather than -7 |
#5
|
|||
|
|||
I made a change to the "applies to" field now everything works perfectly....I have more last question. If I wanted to use the same rule but only highlight rows that had a status other than 'CLOSED' how would I go about it?
|
#6
|
|||
|
|||
Quote:
=AND($A1=today()-6,F1<"Closed") Change F to whichever column your "Closed" is recorded in if not F. Hope that helps. |
#7
|
|||
|
|||
When using =AND($A1=today()-6,F1<"Closed"), the format changes...see attached.
|
#8
|
|||
|
|||
File attached
|
#9
|
|||
|
|||
|
#10
|
|||
|
|||
The current formula is highlighting 7 days before and after today. Is there anyway to highlight only 7 days before?
|
#11
|
|||
|
|||
Quote:
=AND($A1=TODAY()-6,$A1<=TODAY(),$F1<"Closed") |
#12
|
|||
|
|||
Thanks for your help!
|
#13
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating based on Date | Excel Discussion (Misc queries) | |||
Conditional Formating based on Date | Excel Worksheet Functions | |||
Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
Change value based on another cell's value | Excel Discussion (Misc queries) | |||
Conditional Formating for a cell based on another cell's value | Excel Discussion (Misc queries) |