Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating planned VS actual date
Windows XP SP2, MS Excel 2003.
I have a project plan that has target and actual date columns. Format is 03/02/05. Would like to highlight the planned date cell if it is within 2 days of todays date in yellow AND the actual date cell is blank. If the Actual date is filled in, then no formating of the Planned date cell. If the planned date is = or than today and the actual date field is blank, format the planned date cell RED. Any help would be greatly appreciated Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating planned VS actual date
Will assume for sake of example that target date is in column A and actual
date in column B, and you have headers (also that you meant for the red cells you want dates <= today, i.e. past due, not = or ) - then this formula could be typed in as the first condition for conditional formatting in cell B2: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY())) Set up the format you want for the incomplete tasks due today or before in the conditional format here (red). As a second condition add this formula: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY()+2 )) and set up the format for the tasks due within 2 days (yellow). Then copy cell B2 and then highlight entire column B, paste formats. Should now highlight any actual date meeting your conditions. For your conditional format, " wrote: Windows XP SP2, MS Excel 2003. I have a project plan that has target and actual date columns. Format is 03/02/05. Would like to highlight the planned date cell if it is within 2 days of todays date in yellow AND the actual date cell is blank. If the Actual date is filled in, then no formating of the Planned date cell. If the planned date is = or than today and the actual date field is blank, format the planned date cell RED. Any help would be greatly appreciated Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating planned VS actual date
If I understand what you're looking for correctly, you'll need 2
conditions on the conditional formatting on the planned date column. For example, if the top of the planned date column is A1 and the top of the associated actual date column is B1, you might use something like this on cell A1: Conditional Formatting Condition 1: Formula is: =AND(B1="",A1=INT(NOW())) (and format your red highlighting) Condition 2: Formula is: =AND(B1="",INT(NOW())-A1<=2) (and format your yellow highlighting) Condition 1 will be true only if the actual date is blank and the planned date is greater than or equal to today's date (using INT(NOW()) for today's date to filter out the time and look only at the date today). Condition 2 will be checked only if Condition 1 is false, and will be true only if the actual date is blank and the planned date is 1 or 2 days before today's date (because condition 1 would be met if it's on or after today's date). Hope this helps! Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating planned VS actual date
Thank you for the feedback.
Here are the exact conditions, I did not state them correctly F G 1 Target Date Actual Finish Date 2 03/01/05 03/01/05 3 03/01/05 02/28/05 4 03/01/05 03/01/05 5 02/28/05 blank cell 6 02/28/05 03/01/05 7 03/03/05 Assume todays date = 03/01/05 F2 no highlighting, target = Actual date F3 no highlighting actual date complete before target date F4 no highlighting target date = actual date F5 highlight red, target=today or older and no actual date F6 highlight red, actual date was late F7 hightlight yellow (due within 2 days), no actual date Thank you for your help. Bob On Wed, 2 Mar 2005 09:21:03 -0800, "K Dales" wrote: Will assume for sake of example that target date is in column A and actual date in column B, and you have headers (also that you meant for the red cells you want dates <= today, i.e. past due, not = or ) - then this formula could be typed in as the first condition for conditional formatting in cell B2: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY()) ) Set up the format you want for the incomplete tasks due today or before in the conditional format here (red). As a second condition add this formula: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY()+ 2)) and set up the format for the tasks due within 2 days (yellow). Then copy cell B2 and then highlight entire column B, paste formats. Should now highlight any actual date meeting your conditions. For your conditional format, " wrote: Windows XP SP2, MS Excel 2003. I have a project plan that has target and actual date columns. Format is 03/02/05. Would like to highlight the target date cell if it is within 2 days of todays date in yellow AND the actual date cell is blank. If the Actual date is filled in, then no formating of the Planned date cell. If the target date is = or than today and the actual date field is blank, format the planned date cell RED. Any help would be greatly appreciated Thank you |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating planned VS actual date
If I now understand correctly, what you want is this:
Not yet due or done by target date: all OK, no format Due within 2 days, not yet done: highlight yellow Due today but not yet done OR past due and was not done by target: highlight red If that is so, just change the first condition's formula: =(AND(NOT(ISBLANK($A2)),OR(ISBLANK(B2),$A2=TODAY() ,$A2<B2))) substituting in your column letters (F and G) " wrote: Thank you for the feedback. Here are the exact conditions, I did not state them correctly F G 1 Target Date Actual Finish Date 2 03/01/05 03/01/05 3 03/01/05 02/28/05 4 03/01/05 03/01/05 5 02/28/05 blank cell 6 02/28/05 03/01/05 7 03/03/05 Assume todays date = 03/01/05 F2 no highlighting, target = Actual date F3 no highlighting actual date complete before target date F4 no highlighting target date = actual date F5 highlight red, target=today or older and no actual date F6 highlight red, actual date was late F7 hightlight yellow (due within 2 days), no actual date Thank you for your help. Bob On Wed, 2 Mar 2005 09:21:03 -0800, "K Dales" wrote: Will assume for sake of example that target date is in column A and actual date in column B, and you have headers (also that you meant for the red cells you want dates <= today, i.e. past due, not = or ) - then this formula could be typed in as the first condition for conditional formatting in cell B2: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY()) ) Set up the format you want for the incomplete tasks due today or before in the conditional format here (red). As a second condition add this formula: =(AND(NOT(ISBLANK($A2)),ISBLANK(B2),$A2<=TODAY()+ 2)) and set up the format for the tasks due within 2 days (yellow). Then copy cell B2 and then highlight entire column B, paste formats. Should now highlight any actual date meeting your conditions. For your conditional format, " wrote: Windows XP SP2, MS Excel 2003. I have a project plan that has target and actual date columns. Format is 03/02/05. Would like to highlight the target date cell if it is within 2 days of todays date in yellow AND the actual date cell is blank. If the Actual date is filled in, then no formating of the Planned date cell. If the target date is = or than today and the actual date field is blank, format the planned date cell RED. Any help would be greatly appreciated Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating For a Date | Excel Discussion (Misc queries) | |||
More Planned and Actual Cycle Times | Excel Worksheet Functions | |||
how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
Conditional formating w/ Date | Excel Worksheet Functions |