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

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,

"[email protected]" 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
>
>
>

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

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"
<[email protected]> 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,
>
>"[email protected]" 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
>>
>>
>>

Thank you for your help.
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 2 Mar 2005 09:31:24 -0800, [email protected] wrote:

>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

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)

"[email protected]" 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"
> <[email protected]> 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,
> >
> >"[email protected]" 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
> >>
> >>
> >>
>
>