Conditionally format weekends, invalid dates

[Mindlesh]

Given a sheet with 13 columns (A:M), and one row (A32:M32) of month ("mmm") names, how can I conditionally format weekends and (separately) invalid dates? (Column A can be either the current or the next month.)

 

[etaf]

yep
that would work
as posted in my first reply

 

[Mindlesh]

Except that cells are formatted at random: A1; A3; A6; A11; A18:A19; A25:A31.

 

[etaf]

not sure what that means

 

[Mindlesh]

If A32 is Jan-2017, then these cells should be formatted as weekends: A1; A7:A8; A14:A15; A21:A22; A28:A29.

 

[etaf]

you mean highlighted using conditional formatting
same range in B - M
select the range
A1; A7:A8; A14:A15; A21:A22; A28:A29. - M

and then use conditional formatting

see attached sample

https://www.dropbox.com/s/fejtk2w19dfol3m/Cond_Frmt_Non_consec_etaf.xlsx?dl=0

 

[Joe4]

Highlight your range (A1:M32) and use this Conditional Formatting formula:

=WEEKDAY(DATE(YEAR(A$32),MONTH(A$32),ROW()),2)>5

 

[Mindlesh]

Highlight your range (A1:M32) and use this Conditional Formatting formula:

=WEEKDAY(DATE(YEAR(A$32),MONTH(A$32),ROW()),2)>5

This is looking good, thank you; I have just applied the formatting to A1:M31 to avoid treating row 32 as dates. How can I apply a separate format to block out invalid dates; eg. 29-31 Feb 2017?

 

[Joe4]

Try a second conditional formatting formula like this:

=MONTH(DATE(YEAR(A$32),MONTH(A$32),ROW()))<>MONTH(A$32)

And then you can choose black highlighting to black out that cell.

 

[Mindlesh]

Fantastic, thank you!

 

[Joe4]

You are welcome!

The trick there with that one is that with the DATE function, if you enter in too many days, it will not return an error, it spills over to the next month.
For example, =DATE(2017,2,30) will return March 2, 2017
So what I did was simply extract the month from the calculated date for that row, and compare it to the month in Row 32 to see if it was the same or not.