Look Who it Was Random video

Excel - Mastering Excel: How to Find the 2nd Tuesday in November - Episode 386

577 views · Published 20 October 2009 · 3:37 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Formula to Calculate the Second Tuesday of a Month (or any such Nth Weekday question).

Welcome back to the MrExcel netcast, where we answer your Excel questions and provide helpful tips and tricks. I'm Bill Jelen and today we have a great question from Ken in Chicago. He wants to know if there is a way to build a formula that will show the data for every 2nd Tuesday in November. Let's dive into this challenging question and see if we can come up with a solution.

To start off, I have set up a worksheet with the years 1998 to 2007 in column A. Using the DATE function, I can input the year, month, and day to get the desired date. For the year, I will use the numbers in column A, for the month, I know it is November, and for the day, I will start with the 8th as the earliest possible date for the 2nd Tuesday. After entering the formula, I can use the fill handle to drag it down to the rest of the years.

Next, I will use the WEEKDAY function to determine which day of the week the date falls on. A 3 in the result means it is a Tuesday. If the result is 1 or 2, I will need to add 2 or 1 days respectively to get to the 2nd Tuesday. If the result is greater than 3, I will need to add more days to get to the next Tuesday. This may seem complicated, but with a little bit of paper and pencil work, I have come up with a formula that does the job.

The formula states that if the weekday is less than 4, I want to take 3 minus the weekday. Otherwise, I want to take 10 minus the weekday. This will give me the number of days I need to add to the date in cell B1 to get the 2nd Tuesday. After entering the formula and copying it down, we now have the dates for the 2nd Tuesday of every November.

I know this may seem like a difficult and lengthy process, but unfortunately, there is no easy function on the menu that can give us the 2nd Tuesday of every November. However, if you have a better way to figure this out, I would love to hear it! Give me a call at 866-581-0221 and we can feature your solution on a future netcast. And if you have any other Excel questions, don't hesitate to call in and leave a voicemail. We are always looking for new questions to answer and help our viewers with. Thanks for watching and we'll see you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00)
- Call for Excel questions (00:17)
- How to submit a question (00:21)
- Example question from Ken in Chicago (00:32)
- Walkthrough of solution (00:50)
- Using the DATE function (01:04)
- Formatting the date (01:26)
- Using the WEEKDAY function (01:39)
- Formula for finding the 2nd Tuesday of November (02:09)
- Alternative solutions (02:47)
- Call for easier solutions (03:11)
- Conclusion (03:17) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
2nd Tuesday in November
Call 866-581-0221
Ctrl-click and drag
DATE function
Excel formula
Excel question
Fill handle
Format Cells
Function on the menu
WEEKDAY function
Worksheet


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152699/

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!

More from this channel