Conditional format for date



I have a list of dates (column A) say from 01/01/2005 to present and
attaching values at column B. Is it possible to have a conditional format
where the cells will be filled with colours upon data entry? Secondly, the
colours need to be arrange such that for an even year (2006,2008), the cells
is filled with a particular colour, together with the column B values. And
for odd years (2005,2007,2009), both cells are in another colour?



B. R.Ramachandran


Step 1: Select the entire columns A and B (by left-clicking on "A" in the
column-label bar above Row 1, and dragging, while holding the mouse-button,
to "B").
Right click anywhere in the selcted area --> Fomat Cells --> Fill tab -->
pick the color you desire to have for even numbered years.

Step 2: Select the entire columns A and B as in Step 1. "Conditional
Formatting" -->"New Rule"--> "Use a formula..." --> in the formula box enter
the formula
=ISODD(YEAR($A1)) --> "Format" button--> "Fill" tab --> select the color you
desire to have for odd numbered year rows --> "OK"--> "OK".

Step 3: Repeat Step 2, but with the formula, =ISBLANK($A1). This time for
the fill color select "No color"

Hope this works. If this helps, please give a feedback by clicking the
"Yes" button.

B. R. Ramachandran


Hi Biff,

I am using Office 2007. Thanks for responding. Ramachandran has solved my
enquiries. Happy new year to both of you.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question