Ken from Chicago calls with a tough question - how can Excel calculate the 2nd Tuesday of November for a series of years. The solution involves a series of obscure Excel functions. Episode 386 shows you how. If you have a question for the Netcast, call 1-866-581-0221 and leave your message on the recording.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
I've been asking for people to call in with some calls, so we can keep the netcast going.
If you have an Excel question, you can call 866-581-0221, leads to voicemail, just leave a voicemail, say “Hey, this is Joe from Boston” and give us your question, we’ll try to get to it on a future netcast.
I have a great question today from Ken in Chicago, let's listen: “Hi Bill, this is Kevin from Chicago, I have a question for your netcast.
Is there a way to build a formula that would make Excel show the data of every 2nd Tuesday in November?” Wow, that's a great question, and a really hard question, let's see if we can walk through it here in the netcast.
I set up a worksheet, I put 1998 here in a cell, and I'm going to grab the fill handle and ctrl-click and drag, so I can fill in the years, go up to 2007.
And now I’m going to use the DATE function, =DATE is going to ask for a year, a month, and a day.
Well, the year that I want is that number over there in column A, the month, I know, is November, and the day, this is an interesting one.
I know that it's going to be at least the 8th, the 2nd Tuesday, the earliest that that could be would be the 8th of November.
So I'm going to ask for the 8th, just kind of as a temporary formula, and see what we get.
I'll Enter that formula, double-click the fill handle, and now I want to format this to see what day of the week I’m getting.
So if I go to Format Cells and choose the second date format, I can see that some of these fall on a Tuesday and others don’t.
The step that I’m going to need next is the WEEKDAY function.
The WEEKDAY function will give me a number from 1-7, that basically is a code that tells me which day of the week the data is falling on.
It happens that a 3 is a Tuesday, so if I have an answer there of 1 or 2, I know that I need to either add 2 or 1 days in order to get to the result.
If I have an answer that's greater than 3, so in other words, the 8th of the month falls on a Wednesday or beyond, I'm going to have to add a few more days to get up to the next Tuesday.
And the formula there, I’m going to switch to another sheet where I already puzzled out this formula.
It says that if the weekday is less than 4, that I want to take 3 minus the weekday, otherwise I want to take 10 minus the weekday, and that was a little paper and pencil to figure that one out.
And then finally, add that back to the date in cell B1, enter that formula, copy it down, and we will have the date, that basically is the second Tuesday of every November.
Now that's really hard - it is a solution, it would be really nice if we had some sort of a function right on the menu that was able to give us a 2nd Tuesday of every year in November.
Unfortunately, we're kind of stuck with this horrendous formula, but Ken thanks for the call!
Anyone else, if you’d ever like to call with Excel questions, frankly, hopefully easier than that one, give us a call: 866-581-0221.
And I know, with 3000 people out there watching this netcast, someone right now is saying “Bill, you missed something completely obvious, there's an easier way to do that!” Give me a call at the same number: 866-581-0221.
If you have a better way to figure out the 2nd Tuesday of every November, give me a call and we'll get you on a future netcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
I've been asking for people to call in with some calls, so we can keep the netcast going.
If you have an Excel question, you can call 866-581-0221, leads to voicemail, just leave a voicemail, say “Hey, this is Joe from Boston” and give us your question, we’ll try to get to it on a future netcast.
I have a great question today from Ken in Chicago, let's listen: “Hi Bill, this is Kevin from Chicago, I have a question for your netcast.
Is there a way to build a formula that would make Excel show the data of every 2nd Tuesday in November?” Wow, that's a great question, and a really hard question, let's see if we can walk through it here in the netcast.
I set up a worksheet, I put 1998 here in a cell, and I'm going to grab the fill handle and ctrl-click and drag, so I can fill in the years, go up to 2007.
And now I’m going to use the DATE function, =DATE is going to ask for a year, a month, and a day.
Well, the year that I want is that number over there in column A, the month, I know, is November, and the day, this is an interesting one.
I know that it's going to be at least the 8th, the 2nd Tuesday, the earliest that that could be would be the 8th of November.
So I'm going to ask for the 8th, just kind of as a temporary formula, and see what we get.
I'll Enter that formula, double-click the fill handle, and now I want to format this to see what day of the week I’m getting.
So if I go to Format Cells and choose the second date format, I can see that some of these fall on a Tuesday and others don’t.
The step that I’m going to need next is the WEEKDAY function.
The WEEKDAY function will give me a number from 1-7, that basically is a code that tells me which day of the week the data is falling on.
It happens that a 3 is a Tuesday, so if I have an answer there of 1 or 2, I know that I need to either add 2 or 1 days in order to get to the result.
If I have an answer that's greater than 3, so in other words, the 8th of the month falls on a Wednesday or beyond, I'm going to have to add a few more days to get up to the next Tuesday.
And the formula there, I’m going to switch to another sheet where I already puzzled out this formula.
It says that if the weekday is less than 4, that I want to take 3 minus the weekday, otherwise I want to take 10 minus the weekday, and that was a little paper and pencil to figure that one out.
And then finally, add that back to the date in cell B1, enter that formula, copy it down, and we will have the date, that basically is the second Tuesday of every November.
Now that's really hard - it is a solution, it would be really nice if we had some sort of a function right on the menu that was able to give us a 2nd Tuesday of every year in November.
Unfortunately, we're kind of stuck with this horrendous formula, but Ken thanks for the call!
Anyone else, if you’d ever like to call with Excel questions, frankly, hopefully easier than that one, give us a call: 866-581-0221.
And I know, with 3000 people out there watching this netcast, someone right now is saying “Bill, you missed something completely obvious, there's an easier way to do that!” Give me a call at the same number: 866-581-0221.
If you have a better way to figure out the 2nd Tuesday of every November, give me a call and we'll get you on a future netcast.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
