Color Column Based On Dates

[jaimy]

I have my dates put in this way. (Column A, C, E, G, I, K, M)

Mon Tue Wed Thu Fri Sat Sun
01-12 02-12 03-12 04-12 05-12 06-12 07-12
How can I fill color under the columns for first Saturday(column K) of
the month and Sundays (column M)?
(For the month of December, there are one Saturday and 4 Sundays. )

Thanks,
jaimy

 

[Paul]

I have my dates put in this way. (Column A, C, E, G, I, K, M)

Mon Tue Wed Thu Fri Sat Sun
01-12 02-12 03-12 04-12 05-12 06-12 07-12
How can I fill color under the columns for first Saturday(column K) of
the month and Sundays (column M)?
(For the month of December, there are one Saturday and 4 Sundays. )

Thanks,
jaimy

I assume you want the first Saturday in one colour and all Sundays in
another colour. If so, you need to apply conditional formatting with two
conditions. I'm not sure which row your dates are in, so I will assume row
1; you can adjust these formulas if it's different.

Select column A.
Format > Conditional formatting
In "Condition 1", change the first box to "Formula Is".
In the second box put the formula
=AND(WEEKDAY(A$1)=7,DAY(A$1)<8)
Press Format and choose the colour you want for the first Saturday, followed
by OK.
Now press the "Add >>" button for the second condition.
In "Condition 2", change the first box to "Formula Is".
In the second box put the formula
=(WEEKDAY(A$1)=1)
Press Format and choose the colour you want for Sundays, followed by OK.
Now press OK again, and this will apply the conditions to column A.

Finally, use the format painter to copy the formatting from column A and
apply it to each of your other columns (C, E, etc.). Whilst column A is
selected, double-click the format painter button. Now, the format will be
applied to whichever column headers you click, so click on C, E, etc. When
you have finished, click the format painter button again to exit this mode.

 

[jaimy]

Sorry, but I would like the whole column be colored. Let's say from
A10:A20 if col A5 is Saturday. If C5 is Sunday, then C10:C20 are
color.

Then, can we still use the Conditional Formatting to do this?

 

[Paul]

Sorry, but I would like the whole column be colored. Let's say from
A10:A20 if col A5 is Saturday. If C5 is Sunday, then C10:C20 are
color.

Then, can we still use the Conditional Formatting to do this?

Yes. The instructions I gave will colour the whole column. That's why the
first instruction, before going into Conditional formatting, was "Select
column A." It's also why the formulas
=AND(WEEKDAY(A$1)=7,DAY(A$1)<8)
and
=(WEEKDAY(A$1)=1)
contain the $ sign in A$1, so that all cells in the column base their
conditional formatting on the same cell (rather than each using its own
value).

If you only want to colour A10:A20 rather than all of the column, just
select this range rather than the whole column before going ito Conditional
formatting. To base the format on a date in A5, replace A$1 in the formulas
above with A$5.

When you have column A working as required, you get to the final part that
involves copying the column A format with the format painter and applying it
to column C (etc.). This is why the formulas use mixed references (A$1)
rather than absolute ($A$1), so that the basis for the colouring will
automatically adjust. The colouring in column C will depend on C$1 (or C$5
if you adjust as in the last paragraph above) rather than $A$1.