Date Range

[Cathy]

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

 

[David Biddulph]

=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.

 

[Bob Phillips]

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]

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]

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]

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

 

[Dave Peterson]

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.