'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate the First Four Tuesdays and Thursdays of a Month in Excel
Question details
The user needs a method to generate specific recurring dates (the first, second, third, and fourth Tuesdays and Thursdays of every month) for scheduling purposes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an automated scheduling calendar or content plan in Excel for the web.
- Observed behavior
- Requires automated formulas to determine specific weekdays dynamically based on a starting month or date.
Make sure you have a dedicated cell for your starting date (e.g., the 1st of your target month) and that the cell is formatted correctly as a Date.
Use the WEEKDAY and CHOOSE Functions
By combining the WEEKDAY function with CHOOSE, you can calculate the exact number of days to add to your starting date to reach the first target weekday, then simply add 7 for subsequent weeks.
The WEEKDAY function evaluates a date and returns a number representing the day of the week. By setting the return type to 2, Monday becomes 1 and Sunday becomes 7. The CHOOSE function then looks at that number and determines exactly how many days need to be added to reach the next Tuesday or Thursday.
In a blank worksheet, select cell A2 and type the first day of your desired month (e.g., 10/1/2023). Ensure this cell is formatted as a Date.
Select cell C2 and enter the formula: =A2+CHOOSE(WEEKDAY(A2,2),1,0,6,5,4,3,2). This formula checks the weekday of A2 and adds the corresponding number of days needed to land on the very first Tuesday of that month.
In another cell (e.g., D2), enter the formula for the first Thursday: =A2+CHOOSE(WEEKDAY(A2,2),3,2,1,0,6,5,4). This applies the same logic but offsets the addition to target Thursday instead of Tuesday.
To get the subsequent Tuesdays and Thursdays, simply add 7 days to the previous result. For the second Tuesday, enter =C2+7 in cell C3. For the third Tuesday, enter =C3+7 in C4, and so on. Repeat this +7 pattern for your Thursdays in column D.
Easily Automate Your Schedules with WPS Spreadsheet
WPS Office Spreadsheet provides comprehensive support for advanced date and time formulas, including WEEKDAY, CHOOSE, and DATE. You can build automated content calendars and schedules effortlessly while enjoying full compatibility with Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank Spreadsheet.
- 2. Input Your Starting Date: Type the 1st of the month into cell A2 and format it as a Date.
- 3. Apply the Automation Formula: Paste the CHOOSE and WEEKDAY formula to generate your first Tuesday and Thursday, then drag the fill handle down to automatically add 7 days for the rest of the month.
Frequently Asked Questions
How do I calculate the last Tuesday of the month instead of the first?
To find the last Tuesday, you can use the EOMONTH function to find the last day of the month, and then subtract days based on its weekday. A common formula is: =EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-3,7).
What happens if a month has a fifth Tuesday or Thursday?
If you continue adding 7 days (e.g., =C5+7 for the fifth Tuesday), the date might spill over into the next month. You can wrap your formula in an IF statement to check if the month of the newly calculated date matches the month of your starting date. E.g., =IF(MONTH(C5+7)=MONTH($A$2), C5+7, "").
Do these scheduling formulas work across different spreadsheet software?
Yes, functions like WEEKDAY, CHOOSE, and basic date addition operations are universal. Formulas written in Excel for the web will work seamlessly in the desktop version of Excel, WPS Spreadsheet, and Google Sheets.
