Date Range
 

Hello

I am trying to change the colour and display of a cell dependant on if a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1 and the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that the
date was within the range, green or not red.

thanks

Date Range
 

=IF(AND(B1--LEFT(A1,10),B1<--RIGHT(A1,10)),"Within range","Not within
range")
and use Format/ Conditional formatting to colour the cell depending on the
content.
Can change and < to = and <= if the boundary values are to be regarded as
within the range.
--
David Biddulph

"Cathy" wrote in message
...
Hello

I am trying to change the colour and display of a cell dependant on if a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1 and
the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that
the
date was within the range, green or not red.

thanks

Date Range
 

Use conditional formatting (See
http://www.contextures.com/xlCondFormat01.html) with a formula of

=AND(B1=--LEFT(A1,FIND(":",A1)-1),B1<=--MID(A1,FIND(":",A1)+1,10))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Cathy" wrote in message
...
Hello

I am trying to change the colour and display of a cell dependant on if a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1 and
the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that
the
date was within the range, green or not red.

thanks

Date Range
 

thanks for this, however when testing it only ever displays the false outcome
of not within range, it may be how i have displayed the date range in A1, is
there a specific way to do it? many thanks

"David Biddulph" wrote:

=IF(AND(B1--LEFT(A1,10),B1<--RIGHT(A1,10)),"Within range","Not within
range")
and use Format/ Conditional formatting to colour the cell depending on the
content.
Can change and < to = and <= if the boundary values are to be regarded as
within the range.
--
David Biddulph

"Cathy" wrote in message
...
Hello

I am trying to change the colour and display of a cell dependant on if a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1 and
the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that
the
date was within the range, green or not red.

thanks

Date Range
 

I have assumed that your range in A1 is dd/mm/yyyy:dd/mm/yyyy (with no extra
spaces or other additional characters before or afterwards) and that B1 is
an Excel date, rather than text that looks like a date.
To check whether the formats are correct =--LEFT(A1,10) should return 39440,
=--RIGHT(A1,10) should return 39446, and =B1 should return 39442 (assuming
that you format the cells containig these 3 formulae as General).
If you don't get sensible numbers out from =---LEFT(A1,10) look at what you
get from =LEFT(A1,10) (and similarly for the RIGHT formula).

Another contributor gave an alternative formula which ought to be rather
more general than mine in its treatment of A1.
--
David Biddulph

"Cathy" wrote in message
...
thanks for this, however when testing it only ever displays the false
outcome
of not within range, it may be how i have displayed the date range in A1,
is
there a specific way to do it? many thanks

"David Biddulph" wrote:

=IF(AND(B1--LEFT(A1,10),B1<--RIGHT(A1,10)),"Within range","Not within
range")
and use Format/ Conditional formatting to colour the cell depending on
the
content.
Can change and < to = and <= if the boundary values are to be regarded
as
within the range.
--
David Biddulph

"Cathy" wrote in message
...
Hello

I am trying to change the colour and display of a cell dependant on if
a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1
and
the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that
the
date was within the range, green or not red.

thanks

Date Range
 

In my penultimate sentence below the --- should of course be --
[I must get this dodgy keyboard fixed!]
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
I have assumed that your range in A1 is dd/mm/yyyy:dd/mm/yyyy (with no
extra spaces or other additional characters before or afterwards) and that
B1 is an Excel date, rather than text that looks like a date.
To check whether the formats are correct =--LEFT(A1,10) should return
39440, =--RIGHT(A1,10) should return 39446, and =B1 should return 39442
(assuming that you format the cells containig these 3 formulae as
General).
If you don't get sensible numbers out from =---LEFT(A1,10) look at what
you get from =LEFT(A1,10) (and similarly for the RIGHT formula).

Another contributor gave an alternative formula which ought to be rather
more general than mine in its treatment of A1.
--
David Biddulph

"Cathy" wrote in message
...
thanks for this, however when testing it only ever displays the false
outcome
of not within range, it may be how i have displayed the date range in A1,
is
there a specific way to do it? many thanks

"David Biddulph" wrote:

=IF(AND(B1--LEFT(A1,10),B1<--RIGHT(A1,10)),"Within range","Not within
range")
and use Format/ Conditional formatting to colour the cell depending on
the
content.
Can change and < to = and <= if the boundary values are to be
regarded as
within the range.
--
David Biddulph

"Cathy" wrote in message
...
Hello

I am trying to change the colour and display of a cell dependant on if
a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1
and
the
date displayed is 26/12/2007 in B1, how do i get it to state in C1
that
the
date was within the range, green or not red.

thanks

Date Range
 

You may want to split your dates into separate columns. Formulas will get lots
simpler if you do this.

You could use data|text to columns|delimited by colon (:)
and make sure you choose the dmy format for the dates.

I think you'll have a real ugly formula if your windows short date format isn't
in dmy order, too.

If your windows short date format is dmy, then the formula won't be as nice as
separate cells formulas.

Cathy wrote:

Hello

I am trying to change the colour and display of a cell dependant on if a
date is within a range. If the range is 24/12/2007:30/12/2007 in A1 and the
date displayed is 26/12/2007 in B1, how do i get it to state in C1 that the
date was within the range, green or not red.

thanks

--

Dave Peterson