Conditional Formatting (Dates)
 

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.



"Jim" wrote:

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

Conditional Formatting (Dates)
 

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 (MVP - Excel)


"Joel" wrote in message
...
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.



"Jim" wrote:

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

Conditional Formatting (Dates)
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Joel" wrote in message
...
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.



"Jim" wrote:

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