# Conditionally format weekends, invalid dates

#### Mindlesh

##### Board Regular
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.)

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### etaf

##### Well-known Member
if the cell is a correct date and just formatted to show the month
then use a formula for conditional formatting

select the range A32:M32

then use a formula

=WEEKDAY(A32,2)>5
that will highlight weekends

Not sure what you mean by invalid dates
They will be entered as text and so not a number

then enter a 2nd rule and use a formula
=NOT(ISNUMBER(A32))

that will test to see if the cell contains a number, which a valid date will be

Last edited:

#### Mindlesh

##### Board Regular
There are no dates: A1 represents the first day of whatever month is in A32. If A32 is "Jan" then I want to format A1 because 1 Jan 2017 is a Sunday.

#### etaf

##### Well-known Member
and is "JAN" in A32 a text entry or an actual date formatted to only show the month

It is just text.

#### etaf

##### Well-known Member
thats a lot more complicated then
A32:M32 - i assume can have any months in
and so what happens if A32 and C32 have JAN
do you assume that the month is always the 1st ?
and how do we work out what year to choose
will the month always be 1 year ahead of todays date
so if we open the spreadsheet in February and have a month
JAN and FEB and MAR
do we have to assume , that JAN and FEB will be the following year BUT MAR will be this year

so we open the spreadsheet on 2nd feb 2017
then JAN is 1/1/18 and feb is 1/2/18 but Mar is 1/3/17
???

#### Mindlesh

##### Board Regular
The months are consecutive; A32 and M32 are one year apart.

#### etaf

##### Well-known Member
and so does the rule of when open apply
based on today() date ?
so anything in the past or same month is a year ahead and future is current year?

and you are only testing if the 1st of the month is a weekend ?

Last edited:

#### Joe4

But how do you know which year values should be applied?
Is there anything on the sheet in regards to that?

It is important to be detailed in your explanation, and not leave out the important details.
It may be helpful to post an image of what your data looks like.
There are tools you can use to post screen images. They are listed in Section B of this link here: Guidelines for Forum Use.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Last edited:

#### Mindlesh

##### Board Regular
I could format A32:M32 as dates so that, if A32 is Jan-2017, then I can determine that A1 is a Sunday.

Replies
5
Views
299
Replies
1
Views
319
Replies
5
Views
126
Replies
5
Views
173
Replies
0
Views
175

1,195,635
Messages
6,010,831
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back