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.

--
Stuart

[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" <stuartbennett-online@ntlworld.com> wrote in message
news:6E14582D-25FD-4A04-9087-39A26F6B7663@microsoft.com...
> 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.
>
> --
> Stuart

[Stuart]

thanks bob it worked perfectly, once again you have saved my ***.
--
Stuart


"Bob Phillips" wrote:

> 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" <stuartbennett-online@ntlworld.com> wrote in message
> news:6E14582D-25FD-4A04-9087-39A26F6B7663@microsoft.com...
> > 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.
> >
> > --
> > Stuart
>
>
>

[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.
--
Stuart


"Bob Phillips" wrote:

> 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" <stuartbennett-online@ntlworld.com> wrote in message
> news:6E14582D-25FD-4A04-9087-39A26F6B7663@microsoft.com...
> > 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.
> >
> > --
> > Stuart
>
>
>

[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)

"Stuart" <stuartbennett-online@ntlworld.com> wrote in message
news:05D572ED-F840-4D28-BBCA-A403D6B414BA@microsoft.com...
> 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.
> --
> Stuart
>
>
> "Bob Phillips" wrote:
>
> > 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" <stuartbennett-online@ntlworld.com> wrote in message
> > news:6E14582D-25FD-4A04-9087-39A26F6B7663@microsoft.com...
> > > 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.
> > >
> > > --
> > > Stuart
> >
> >
> >