Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - due date
I need some help with Conditional Formatting, please.
I have a spreadsheet used to track data relating to reports prepared for clients. Column F on each row contains the date the report is due. Column G contains the actual date when the completed report is submitted. I would like to add conditional formatting to Column F. I can manage the first part, which would add yellow background to cells with dates within the next 3 days and red background to cells where the report is due today or overdue. However, the part I am stumped on is: I would like to have no background once any value is entered into column G. Once the report has been submitted, there is no longer any need to have color alerts, and it is not helpful to have a bunch of red cells where the report has already been submitted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - due date
Hi Rod
Add an AND to your formatting formulae =AND(G1="",TODAY()-F1=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... I need some help with Conditional Formatting, please. I have a spreadsheet used to track data relating to reports prepared for clients. Column F on each row contains the date the report is due. Column G contains the actual date when the completed report is submitted. I would like to add conditional formatting to Column F. I can manage the first part, which would add yellow background to cells with dates within the next 3 days and red background to cells where the report is due today or overdue. However, the part I am stumped on is: I would like to have no background once any value is entered into column G. Once the report has been submitted, there is no longer any need to have color alerts, and it is not helpful to have a bunch of red cells where the report has already been submitted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - due date
Thanks for the help, Roger, but I couldn't get it to work. I am a novice at
Conditional Formatting and functions, so I probably got the syntax wrong. Do you mean to add this string [=AND(G4="",TODAY()-F4=0)] as one of the Conditions (i.e. condition #3), or to add it to one of the existing conditions? I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4=0), but got a syntax error. "Roger Govier" wrote: Hi Rod Add an AND to your formatting formulae =AND(G1="",TODAY()-F1=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... I need some help with Conditional Formatting, please. I have a spreadsheet used to track data relating to reports prepared for clients. Column F on each row contains the date the report is due. Column G contains the actual date when the completed report is submitted. I would like to add conditional formatting to Column F. I can manage the first part, which would add yellow background to cells with dates within the next 3 days and red background to cells where the report is due today or overdue. However, the part I am stumped on is: I would like to have no background once any value is entered into column G. Once the report has been submitted, there is no longer any need to have color alerts, and it is not helpful to have a bunch of red cells where the report has already been submitted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - due date
Hi Rod
Your 1st Condition for Yellow format needs to be Formula IS =AND(G4="",F4-TODAY()<4) Your 2nd condition for the Red Format needs to be Formula Is =AND(G4="",TODAY()-F4=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... Thanks for the help, Roger, but I couldn't get it to work. I am a novice at Conditional Formatting and functions, so I probably got the syntax wrong. Do you mean to add this string [=AND(G4="",TODAY()-F4=0)] as one of the Conditions (i.e. condition #3), or to add it to one of the existing conditions? I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4=0), but got a syntax error. "Roger Govier" wrote: Hi Rod Add an AND to your formatting formulae =AND(G1="",TODAY()-F1=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... I need some help with Conditional Formatting, please. I have a spreadsheet used to track data relating to reports prepared for clients. Column F on each row contains the date the report is due. Column G contains the actual date when the completed report is submitted. I would like to add conditional formatting to Column F. I can manage the first part, which would add yellow background to cells with dates within the next 3 days and red background to cells where the report is due today or overdue. However, the part I am stumped on is: I would like to have no background once any value is entered into column G. Once the report has been submitted, there is no longer any need to have color alerts, and it is not helpful to have a bunch of red cells where the report has already been submitted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting - due date
Perfect! Thanks very much, Roger.
"Roger Govier" wrote: Hi Rod Your 1st Condition for Yellow format needs to be Formula IS =AND(G4="",F4-TODAY()<4) Your 2nd condition for the Red Format needs to be Formula Is =AND(G4="",TODAY()-F4=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... Thanks for the help, Roger, but I couldn't get it to work. I am a novice at Conditional Formatting and functions, so I probably got the syntax wrong. Do you mean to add this string [=AND(G4="",TODAY()-F4=0)] as one of the Conditions (i.e. condition #3), or to add it to one of the existing conditions? I tried to use =(F4-TODAY())<=1 AND(G4="",TODAY()-F4=0), but got a syntax error. "Roger Govier" wrote: Hi Rod Add an AND to your formatting formulae =AND(G1="",TODAY()-F1=0) -- Regards Roger Govier "Rod from Corrections" wrote in message ... I need some help with Conditional Formatting, please. I have a spreadsheet used to track data relating to reports prepared for clients. Column F on each row contains the date the report is due. Column G contains the actual date when the completed report is submitted. I would like to add conditional formatting to Column F. I can manage the first part, which would add yellow background to cells with dates within the next 3 days and red background to cells where the report is due today or overdue. However, the part I am stumped on is: I would like to have no background once any value is entered into column G. Once the report has been submitted, there is no longer any need to have color alerts, and it is not helpful to have a bunch of red cells where the report has already been submitted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting date ranges | Excel Worksheet Functions | |||
WEEKDAY in Conditional Formatting? | Excel Worksheet Functions | |||
Mileage Claim Formula | New Users to Excel | |||
Conditional Formatting Date | New Users to Excel | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) |