Conditional Formatting (Dates)

  • Thread starter Thread starter Jim
  • Start date Start date
J

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
 
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.
 
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.
 
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.
 
Back
Top