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.

Start Your IELTS Preparation in 3 Simple Steps

Start Your IELTS Preparation in 3 Simple Steps

– Hi, everyone. Chris here from IELTS Advantage. And in this lesson, we’re going to look at how to startRead More Start Your IELTS Preparation in 3 Simple Steps

Finesse Mitchell Wants You To ‘Responsibly’ Make Superbowl Monday A Personal Holiday

Finesse Mitchell Wants You To ‘Responsibly’ Make Superbowl Monday A Personal Holiday

Related posts: ‘Love Actually’ Star Breaks Down Why The Holiday Flick Still Warms Hearts 16 Years Later Ben Feldman GetsRead More Finesse Mitchell Wants You To ‘Responsibly’ Make Superbowl Monday A Personal Holiday

Zach Woods Made Eye Contact with a Tandem Bicyclist While Naked in an Outdoor Shower

Zach Woods Made Eye Contact with a Tandem Bicyclist While Naked in an Outdoor Shower

Hello, Zach. Hello, Ellen. Happy holidays to you. The holidays are over but I still say it because I haven’tRead More Zach Woods Made Eye Contact with a Tandem Bicyclist While Naked in an Outdoor Shower

Life Will Not Give You What You Need

Life Will Not Give You What You Need

Related posts: Check out Chipotle Mexican Grill (CMG). Is this "touch and go" ready to fly? (June 27,.. All AboutRead More Life Will Not Give You What You Need

Leave a Reply

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