Morne sends in a question about highlighting records that fall on a weekday. There are a couple of approaches. Episode 901 shows you how.
Transcript of the video:
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.
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.
