| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP - Conditional Formatting Dates & Blank Cells
I have two columns of data:
Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP - Conditional Formatting Dates & Blank Cells
=AND(B1<"",B1=TODAY(),B1<=TODAY()+30)
should do the first -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I have two columns of data: Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP - Conditional Formatting Dates & Blank Cells
I used 'Formula is' and pasted what you wrote below and it did not work. Any
other ideas? I am thorougly frustrated. "Bob Phillips" wrote: =AND(B1<"",B1=TODAY(),B1<=TODAY()+30) should do the first -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I have two columns of data: Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP - Conditional Formatting Dates & Blank Cells
Diane,
Instead of "Formula is", try "Cell Value is" option in the same drop down list and use the following: condition 1: less than =Today() (Format: Font Bold, Color Red) condition 2: between =Today() and =Today()+30 (Format: Font Bold, Color Green) Pl tell me whether I have understood your requirement correctly. Balan "Diane1477" wrote: I used 'Formula is' and pasted what you wrote below and it did not work. Any other ideas? I am thorougly frustrated. "Bob Phillips" wrote: =AND(B1<"",B1=TODAY(),B1<=TODAY()+30) should do the first -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I have two columns of data: Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP - Conditional Formatting Dates & Blank Cells
What does 'did not work' mean exactly?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I used 'Formula is' and pasted what you wrote below and it did not work. Any other ideas? I am thorougly frustrated. "Bob Phillips" wrote: =AND(B1<"",B1=TODAY(),B1<=TODAY()+30) should do the first -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I have two columns of data: Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP - Conditional Formatting Dates & Blank Cells
I wanted to post what finally worked...and elimated the formatting of blank
cells (that was what was not working when I tried the originally sugested solution) Condition 1: cell value is: ="" (No format set) Condition 2: formula is: =DAYS360(S3,TODAY())0 (Format Fill Red & Bold) Condtion 3: cell value is: =TODAY()+60 (Format Green) Thanks to both of you for your help!! "Balan" wrote: Diane, Instead of "Formula is", try "Cell Value is" option in the same drop down list and use the following: condition 1: less than =Today() (Format: Font Bold, Color Red) condition 2: between =Today() and =Today()+30 (Format: Font Bold, Color Green) Pl tell me whether I have understood your requirement correctly. Balan "Diane1477" wrote: I used 'Formula is' and pasted what you wrote below and it did not work. Any other ideas? I am thorougly frustrated. "Bob Phillips" wrote: =AND(B1<"",B1=TODAY(),B1<=TODAY()+30) should do the first -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diane1477" wrote in message ... I have two columns of data: Column A (Current Review Date) Column B (Next Review Date (2 years out) I want to conditionally format so that the reviews due within 30 days of today are highlighted and the reviews overdue are also highlighted but that cells containing no date remain unchanged. I have tried multiple variations of the Today +30 and the =AND(B1<"",B1<TODAY()) formulas with no success. SEE BELOW: Example Data: Column A Column B 1. 9/1/05 9/1/07 (This would appear in RED) 2. 10/1/05 10/1/07 (This would appear in GREEN) 3. blank blank (This would be unaffected by the conditional format) 4. 1/5/06 1/5/08 PLEASE HELP! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting: format when range of cells are blank | Excel Discussion (Misc queries) | |||
| Conditional Formatting - allowing for Blank Cells | Excel Discussion (Misc queries) | |||
| Is conditional formatting only blank, unused cells possible? | Excel Worksheet Functions | |||
| using conditional formatting - blank cells | Excel Discussion (Misc queries) | |||
| Conditional formatting blank cells | Excel Discussion (Misc queries) | |||
Linear Mode
