Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
return a date from range, date is between dates in two other cells | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |