conditional formating - ECD Formatting

[Stuart]

ok more accident management stuff.

i do reports for our customers part of these reports are 2 fields.

Original Estimated Completion Date (Column P)
Revised Estimated Completion Date (Column S)

the jobs start on row 7 onwards.

basically i need 2 formulaes.

the first is based in Column P & will check to see if the value in P of that
row is less than the value returned by "=TODAY()" if it does and the value in
that rows S Column is blank then format with red background and white text.

the second is based in Column S & will check to see if a value is in Column
P if theres a value in Column P and the value in Column S is less than the
value returned by "=TODAY()" then format with red background and white text.

this will remind the person completing the form to make sure that the ECD's
are kept up to date, if theres a value in Column S that meets Column S's red
background condition it will trigger the same formatting in Column P and if
Column P goes red when Column S Gets Updated to be in the future of present
day then both Columns P & S will return to normal formating.

i hope someone can understand exactly what i want it to do and provide the
necesarry formulae's. look forward to reading your replies to this.

 

[Bob Phillips]

Assuming row 2, select column P and S

It can all be done in one formula as the formatting is the same


=OR(AND($P2<TODAY(),$S2=""),AND($P2<>"",$S2<TODAY()))
--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Stuart]

thanks bob it worked perfectly, once again you have saved my ass.

 

[Stuart]

ok found a glitch, when i put the ECD in Column P to 17/08/06 a date in the
future of the value returned by "=TODAY()" it puts it as red with white text,
the idea of how it should work is as follows.

the repairer gives us an ECD (estimated completion date) we key this in as
the original ECD this value will never change from this point onwards, once
we get to day After ECD column P should turn red to indicate that vehicle
hasnt left so ECD needs updating, we then need to obtain a new ECD once a new
ECD is entered which will always then go into column S as long as the ECD
remains valid (in the future of present day or equal to present day then both
fields to be formatted normally, when ECD in column S is in the past of
present day both columns P & S are to go red to indicate that Column S ECD
needs updating. thanks in advance.

 

[Bob Phillips]

Stuart,

Not sure I fully understand as it seems to me that you are saying that if
both dates are in the past they should both be red, whereas I would have
thought that this meant that the ECD is gone, and the actual is gone.

Anyway, let's go with this and see where this takes us

In P2, use the formula

=OR(AND(P2<=TODAY(),S2=""),AND(P2<=TODAY(),S2<>"",S2<=TODAY()))

In S2, use

=AND(P2<=TODAY(),S2<>"",S2<=TODAY())

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)