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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
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
and is "JAN" in A32 a text entry or an actual date formatted to only show the month
 
Upvote 0
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
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
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
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,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top