Based on year, if intersection of month and day is Saturday or Sunday, format cell.

Calander.png

I have a calander that needs to be set up per the above/attached format. The weekend days are shaded in blue. Anyone know of a way to get the weekend days to format to blue based on the year typed in cell A1?

Basically, I need it to look at the year in A1, and if the intersect of the Month and Day fo that year falls on a Saturday or Sunday, I need it to format that cell.

I want this to update when the year is changed in A1.

Thanks,

John

Hello John,

Let's assume that table is in A2:AF14 with the year in A1 (months in A3:A14, day numbers in B2:AF2)

Try this to conditionally format all weekends blue

Select the whole range of date cells, i.e. B3:AF14 and use this formula in conditional formatting

=WEEKDAY(B$2&$A3&$A$1,2)>5

choose blue format > OK

[if you want to get the "non days" at the end of the month in black, (bearing in mind that February 29th at least is variable) you can use this formula in another condition - =ISERROR((B$2&$A3&$A$1)+0)]

Is this a calendar to allow data to be filled in on each date or is it just a presentation?

If you do it the way I suggested that should happen automatically - just change the year and the formatting will change

Tested......works as daddylonglegs has stated.

Don't do any manual clearing of the formatting in the calendar as it will mess up the Conditional Formatting.