Calculate the Second Sunday or Third Thursday of a Month in Excel
Question details
Calculate recurring dates such as the second Sunday or third Thursday of a selected month.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants a reusable formula for finding the nth occurrence of a weekday in a current or future month.
- Observed behavior
- The formula should return a date for the requested weekday, occurrence, and month.
For the named function, check that your Excel version supports LAMBDA, LET, and XMATCH. Decide whether to specify the target month as an offset from today or as a date in a worksheet cell.
Create a reusable Excel LAMBDA for recurring weekday dates
Use a named function when you want to calculate weekday occurrences in the current month or a future month.
In Excel, select Formulas > Name Manager, then select New.
Enter FutureDate as the name. In Refers to, enter =LAMBDA(months,dayOfWeek,NthWk,LET(mm,EOMONTH(TODAY(),months-1),do,XMATCH(dayOfWeek,{"Mon","Tues","Wed","Thur","Fri","Sat","Sun"})+10,mm+1-WEEKDAY(mm,do)+7*NthWk)). Select OK, then close Name Manager.
In a worksheet cell, enter =FutureDate(1,"Sun",2) for the second Sunday of next month, or =FutureDate(1,"Thur",3) for the third Thursday of next month. Use 0 instead of 1 for the current month.
If the result appears as a number, select the cell and choose a date format from Home > Number Format.

Calculate an nth weekday from a date in a cell
Use this formula when you prefer to choose the target month directly in the worksheet without creating a named LAMBDA.
Find the nth weekday of a month in WPS Spreadsheets
Use a worksheet formula in WPS Spreadsheets to calculate dates such as the second Sunday or third Thursday of a selected month.
- 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the month: In A1, enter any valid date in the month you want to calculate.
- 3. Choose the weekday: In B1, enter 1 for Sunday through 7 for Saturday. In C1, enter the occurrence number, such as 2 or 3.
- 4. Calculate and format the result: In D1, enter =DATE(YEAR(A1),MONTH(A1),1)+MOD(B1-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7*(C1-1). Select D1 and apply a date format if needed.

Frequently Asked Questions
How do I calculate the second Sunday of next month?
After defining FutureDate in Formulas > Name Manager, enter =FutureDate(1,"Sun",2) in a worksheet cell.
How do I calculate the third Thursday of the current month?
Enter =FutureDate(0,"Thur",3) after creating the named function. The result may be in the past if that Thursday has already occurred.
Why does my formula return a date in the following month?
A month may have only four occurrences of a particular weekday. If you request a fifth occurrence, check whether the result is still in the target month.
Why does Excel display a number instead of a date?
Excel stores dates as serial numbers. Select the result cell and choose a date format from Home > Number Format.
Can I calculate the date without LAMBDA?
Yes. Enter a date in A1, a weekday number in B1, and an occurrence number in C1, then use the cell formula in the second solution.




