Conditional Formatting (Dates)

[Jim]

Can you use conditional formatting to query a range of cells (dates) then
input from a list based on the outcome in another column? If date range is
between 12/1/09 - 12/24/09 then P1.

For example;
Column G: Date
Data: 12/31/09

Column P: Month
Data: "Based on the return from the CF" could be a list of returns

Thanks

 

[Joel]

When using a dta in a formula you need to use DATEVALUE("12/1/09")

If you have a monthd Like January you have to make a string containing a
date before you use it in a formula

=MONTH(DATEVALUE(F1 & " 1, " & YEAR(TODAY()))) Where F1 is January

this wil produce
=Month("Janury 1, 2009") Look at the spaces carfully. there must be a
space between the comma and 2009.

 

[Rick Rothstein]

You could also construct the text date for the DATEVALUE function as in the
formula below and not have to worry about the space or comma at all...

=MONTH(DATEVALUE("1"&F1&YEAR(TODAY())))

Also, because Excel will attempt to convert things that look like dates into
real dates when used in a calculation, you can eliminate the need for the
DATEVALUE function altogether like this...

=MONTH(--("1"&F1&YEAR(TODAY())))

Here I used the double unary (-- which is the same as multiplying by minus
one twice) to force the calculation, but you could just as easily use 1* or
0+ in place of the -- and the formula would work the same.

 

[Rick Rothstein]

Just so it is clear to those reading this thread, the text string I have
constructed has the pattern dmmmyyyy (for example, 12Mar2009) or dmmmmyyyy
(for example, 12March2009) which Excel has no trouble processing (as a
matter of fact, the year can be a 2-digit year and Excel will construct a
proper date from it)... the key to this format is the month is abbreviated,
or spelled out, in text with the day and year on either side of it.