Conditionally format weekends, invalid dates

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
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
Joined
Oct 24, 2012
Messages
7,315
Office Version
  1. 365
Platform
  1. MacOS
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:
Upvote 0

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
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.
 
Upvote 0

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
7,315
Office Version
  1. 365
Platform
  1. MacOS
and is "JAN" in A32 a text entry or an actual date formatted to only show the month
 
Upvote 0

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
7,315
Office Version
  1. 365
Platform
  1. MacOS
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
???
 
Upvote 0

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
ADVERTISEMENT
The months are consecutive; A32 and M32 are one year apart.
 
Upvote 0

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
7,315
Office Version
  1. 365
Platform
  1. MacOS
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:
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,031
Office Version
  1. 365
Platform
  1. Windows
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:
Upvote 0

Mindlesh

Board Regular
Joined
Apr 2, 2014
Messages
172
I could format A32:M32 as dates so that, if A32 is Jan-2017, then I can determine that A1 is a Sunday.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top