Translating Dates to a Visual

[english4518]

I have the following basic spreadsheet, and I wanted to use conditional
formating to color the relevant cells Green, but in the example given I can
only apply green to Jan

1 2 3 4 5
A Start End Jan Feb Mar (etc)
B 01/01/10 02/02/10

 

[Gord Dibben]

I think you have your rows and columns mixed up to start with.

Columns are the letters across the top and rows are the numbers down the
left side.

Which would be the relevant cells and how do you decide?


Gord Dibben MS Excel MVP

 

[pshepard]

Hi English,

Excel 2007 -

The following will format Jan and Feb green, assuming that the column head
for Jan is actually 1/1/2010 - with a custom format of "mmm" to display as
"Jan" - and so on for all of the column heads, beginning with C1 through N1
for December - 12/1/2010 custom format of "mmm" to display as "Dec".

Next select cell C2 through the last cell in the range to be conditionally
formatted; from the Home ribbon select Conditional Formatting/New Rule/Use a
formula to determine which cells to format/

paste the following formula into Format values where this formula is true:

=IF(OR(AND(MONTH(C$1)=MONTH($A2),YEAR(C$1)=YEAR($A2)),AND(MONTH(C$1)=MONTH($B2),YEAR(C$1)=YEAR($B2))),1,"")

then select the desired formatting.

for the row where 1/1/2010 is start, and 2/2/2010 is end - both Jan and Feb
will be formatted. In your original post you mention that Jan is formatted -
is that what you are wanting, or what you want to fix?

Additional logic can be added to fit your needs - however you need the cells
to be conditionally formatted.

Hope this helps.