Prev: Saving collected data?
Next: How can I regain control of a shared spreadsheet
From: dennisthe10 on 18 Mar 2010 07:58
Hi
I have a date in H4 where using conditional formatting, will flag green if
its within 2 days of today, amber if 2-3 days of today and red if 3+ days
from today however, i need the formatting to stop if there has been a date
entered into I4 (as this will mean there is no need for the cell to flag to
us that there is no data in I4 if that makes sense)

can anyone help please?

thanks

Dennis
From: Mike H on 18 Mar 2010 08:22
What do you mean by

>i need the formatting to stop if there has been a date
> entered into I4

What colour should the cell be?
No colour or the colour it was when the date was entered into I4
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"dennisthe10" wrote:

> Hi
> I have a date in H4 where using conditional formatting, will flag green if
> its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> from today however, i need the formatting to stop if there has been a date
> entered into I4 (as this will mean there is no need for the cell to flag to
> us that there is no data in I4 if that makes sense)
>
> can anyone help please?
>
> thanks
>
> Dennis
From: Mike H on 18 Mar 2010 08:26
This isn't very clear either.

> I have a date in H4 where using conditional formatting, will flag green if
> its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> from today however

Is it 2 days before or after the date in H4 or both?

Post you CF formulas
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"dennisthe10" wrote:

> Hi
> I have a date in H4 where using conditional formatting, will flag green if
> its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> from today however, i need the formatting to stop if there has been a date
> entered into I4 (as this will mean there is no need for the cell to flag to
> us that there is no data in I4 if that makes sense)
>
> can anyone help please?
>
> thanks
>
> Dennis
From: dennisthe10 on 18 Mar 2010 08:32
Hi Mike

Apologies for being unclear. I would need the cell to revert to no colour if
a date is added to the corresponding I cell.

The formatting is set so that the date entered in cell H4 changes colour
dependant on the difference from the date entered compared to todays date,
i.e. if the date entered was 15/03/10 (UK format) then it would change to
amber/orange to indicate 3 days have passed however, if a date is then
entered into I4 then i need it to revert back to no colour

hope this makes a bit more sense

thanks

"Mike H" wrote:

> This isn't very clear either.
>
> > I have a date in H4 where using conditional formatting, will flag green if
> > its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> > from today however
>
> Is it 2 days before or after the date in H4 or both?
>
> Post you CF formulas
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "dennisthe10" wrote:
>
> > Hi
> > I have a date in H4 where using conditional formatting, will flag green if
> > its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> > from today however, i need the formatting to stop if there has been a date
> > entered into I4 (as this will mean there is no need for the cell to flag to
> > us that there is no data in I4 if that makes sense)
> >
> > can anyone help please?
> >
> > thanks
> >
> > Dennis
From: Mike H on 18 Mar 2010 08:52
Hi,

You include an AND statement in the CF formula.

=AND(I4="",Your CF Formula)

this is an example for more than 2 days past

=AND(I4="",H4<TODAY()-2)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"dennisthe10" wrote:

> Hi Mike
>
> Apologies for being unclear. I would need the cell to revert to no colour if
> a date is added to the corresponding I cell.
>
> The formatting is set so that the date entered in cell H4 changes colour
> dependant on the difference from the date entered compared to todays date,
> i.e. if the date entered was 15/03/10 (UK format) then it would change to
> amber/orange to indicate 3 days have passed however, if a date is then
> entered into I4 then i need it to revert back to no colour
>
> hope this makes a bit more sense
>
> thanks
>
> "Mike H" wrote:
>
> > This isn't very clear either.
> >
> > > I have a date in H4 where using conditional formatting, will flag green if
> > > its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> > > from today however
> >
> > Is it 2 days before or after the date in H4 or both?
> >
> > Post you CF formulas
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "dennisthe10" wrote:
> >
> > > Hi
> > > I have a date in H4 where using conditional formatting, will flag green if
> > > its within 2 days of today, amber if 2-3 days of today and red if 3+ days
> > > from today however, i need the formatting to stop if there has been a date
> > > entered into I4 (as this will mean there is no need for the cell to flag to
> > > us that there is no data in I4 if that makes sense)
> > >
> > > can anyone help please?
> > >
> > > thanks
> > >
> > > Dennis
 |  Next  |  Last
Pages: 1 2
Prev: Saving collected data?
Next: How can I regain control of a shared spreadsheet