Learn Excel from MrExcel Episode 901 – Highlight Weekends

Learn Excel from MrExcel  Episode 901 – Highlight Weekends


Back to the MrExcel netcast, I’m Bill Jelen. Hey, a couple of firsts today– first podcast
ever from West Palm Beach, Florida, at least for me; and first time we have a question
that’s sent in from Afghanistan. This is Mornay. Mornay is trying to figure out how to highlight
all of the records that fall on a weekend. So I would approach this using the WEEKDAY
function–=WEEKDAY. The return type– I can never remember the
return type. I know there’s three of them, so I’m just
going to come up here and type all three of them- 1, 2, and 3– then we’ll build a formula
here.=WEEKDAY, go grab that date over there in
Column A using that return type up in Row 2. And so we’ll copy this down and over. Alright. So here’s Monday. I want the one that is return type 2, because
that’s where Monday is a 1 and then it’s very nice that the Saturdays and Sundays fall greater
than 5– basically, 6 or 7. So now, to apply that conditional formatting,
we’ll go to Format, Conditional Formatting, and I’ll say I’m going to change it from Cell
Value Is to Formula Is. I’ll build a formula here that’s going to
refer to Cell A3. That’s the active cell and so we say,=WEEKDAY($A3,2)>5
and if all of that’s true, then we’ll choose a format. So we’ll click OK, click OK, see the weekends
are highlighted. And then if I change the starting date– so
let’s put in 12/5 or 12/4/2008– it instantly updates. Alright. So that’s how I would approach it. Mornay actually sent in his own answer, and
his is pretty cool too. If we go to Format, Conditional Formatting. Didn’t use the WEEKDAY function; uses the
TEXT function– took the text to that date. And think about it, at least in English we’re
going to get Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, the weekends start
with “S”, so the only days that start with S. So he just checked to see if the left character
is an “S” and then if that’s true. But then if it’s not true, he added a second
condition out here that says, “Hey, go look for the date within this range of public holidays,”
and that also might be true, in which case it’s going to return a number greater than
1– that’s treated as true. And so the advantage here with Mornay’s solution
is that it also can highlight the holidays. So here, Christmas and the day after Christmas
are highlighted as well. So actually a fairly cool way to go. So a couple of different solutions to the
problem. The shortcoming with this is, if you’re using
a language other than English, you may not get the same results because your days of
the week are going to be spelled differently. Weekday is probably a safer way to go there. Want to thank Mornay for sending that question
in, want to thank you for stopping by. We’ll see you next time for another netcast
from MrExcel.

At least 71 countries restricting entry of people traveling from S. Korea

over 70 countries are restricting entry from South Korea due to the rapid spread of Kovac 19 in the countryRead More At least 71 countries restricting entry of people traveling from S. Korea

Husband’s weekend morning routine (two nice bois)

Husband’s weekend morning routine (two nice bois)

Our cozy morning routine we’re currently staying at Max’s cousins place the apartment is cute and tiny this is theRead More Husband’s weekend morning routine (two nice bois)

Leave a Reply

Your email address will not be published. Required fields are marked *