Highlight weekend dates

Question

I am working on a spreadsheet where I wish to highlight various activities on the dates they occur and display it in a Perpetual Calendar format.

Cell AD2 contains the 'year' (formatted as General, and set up with a Defined Name of 'TheYear'.

Cells B4, B12, B19, etc contains the 'month' (with the formula "=DATE(TheYear,1,1)", "=DATE(TheYear,2,1)" etc and formatted as 'mmmm').

I want to add the dates of the month in cells C4:AG4 (for the month of January). February dates will be in cells C12:AG12, March dates will be cells C19:AG19, etc.

Questions:

1. I would like to add the day of the week under each of the dates. For example; cells C5:AG5 will have the days of the week corresponding to the January date,  C13:AG13 will have the days of the week corresponding to February dates, etc. How can I do this?
2. How can I highlight the weekend dates? (in the rows containing the dates of the month (ie; C4:AG4 - for the month of January).

This will be a Perpetual Calendar so the above will need to reflect the year as it changes.

Answer 1

For the first question, I'm assuming that C4:AG4 (and C12:AG12 and C19:AG19, etc) hold just a number like 1, 2, 3...  31 for the day of the month.

Put this formula into C5:

=WEEKDAY(DATE(TheYear,MONTH($B4),C4))

and then fill it out to column AG and then format all of them Custom with dddd and they should then show Sunday, Monday, Tuesday, etc.

Now for the highlighting of weekends.  Select all of the C4:AG4 cells and use conditional formatting and create a new rule based on a formula.  Here is the formula to enter:

=OR(WEEKDAY(C5)=1,WEEKDAY(C5)=7)

and choose the formatting to highlight those cells.

You can copy the formulas and formatting down into the other cells in the months that follow on the sheet.

Hope this helps