'%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 Third Wednesday of a Month in Excel
Question details
The user needs an Excel formula to dynamically calculate the exact calendar date of the third Wednesday of a specific month.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Forecasting monthly dates for events such as Social Security payment deposits, recurring meetings, or identifying potential budget shortfalls.
- Observed behavior
- The user requires a formula that evaluates the first day of a target month and accurately returns the date of the third Wednesday.
Ensure you have a cell (such as A2) that contains the first day of your target month, formatted correctly as a date in your spreadsheet.
Use the Standard DATE and WEEKDAY Formula
This is the most reliable approach that works across all versions of Excel and WPS Spreadsheet to find the third Wednesday.
This formula uses the WEEKDAY function to locate the Wednesday in the week containing the fourth day of the month, and then subtracts that from a fixed offset to return the correct exact date.
Click on an empty cell where you want the calculated third Wednesday date to be displayed.
Assuming cell A2 contains the first day of the target month, type the following formula: =DATE(YEAR(A2),MONTH(A2),22)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),4))
Press Enter. If the result appears as a string of numbers, navigate to the Home tab on the top ribbon, click the Number Format dropdown, and select Short Date.
Use the LET Formula for Modern Excel Versions
If you are using Microsoft 365 or Office 2021, you can use the LET function to declare variables for a cleaner, more readable formula.
Generate the Third Wednesday for the Whole Year
Use a dynamic array formula in Microsoft 365 to instantly generate a list of the third Wednesday for every month of the current year.
Calculate Complex Dates Easily with WPS Spreadsheet
WPS Office fully supports advanced date and time formulas, including DATE, WEEKDAY, and dynamic calculations. You can seamlessly manage schedules, track monthly forecasts, and calculate calendar dates in a lightweight, user-friendly interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the base dates.
- 2. Enter your formula: Select your target cell and type your preferred date calculation formula, such as the standard DATE and WEEKDAY combination.
- 3. Apply Date formatting: Right-click the result, choose Format Cells, and select your desired Date format to view the calendar day.
Frequently Asked Questions
Can I adapt this formula to find a different weekday, like the third Thursday?
Yes. In the standard formula =DATE(YEAR(A2),MONTH(A2),22)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)), you are basing the logic on the 4th day of the week (Wednesday). To find Thursday, adjust the formula logic to target the 5th day of the week accordingly.
Why is my formula returning a 5-digit number instead of a date?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. To fix this, right-click the cell, select 'Format Cells', navigate to the 'Number' tab, and apply a 'Date' format.
Does the LET formula work in older versions of Excel?
No, the LET and SEQUENCE functions were introduced in Microsoft 365 and Office 2021. If you are using Excel 2019 or older, you must use the standard DATE and WEEKDAY formula.
Can I use today's date to find the third Wednesday of the current month?
Yes. Instead of referencing cell A2, you can nest the TODAY() function inside the formula to dynamically evaluate the current month and year whenever the spreadsheet is opened.
