Function Problems

Calculate the Second Sunday or Third Thursday of a Month in Excel

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Calculate recurring dates such as the second Sunday or third Thursday of a selected month.

Calculate the Second Sunday or Third Thursday of a Month in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Name Manager

In Excel, select Formulas > Name Manager, then select New.

2
Define the function

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.

3
Calculate a date

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.

4
Format the result

If the result appears as a number, select the cell and choose a date format from Home > Number Format.

Create a reusable Excel LAMBDA for recurring weekday dates
Check the weekday and occurrence: Use the exact weekday names in the function's list, such as "Tues" and "Thur". A requested fifth occurrence can fall in the following month if the target month has only four; a date in the current month can also be in the past.
Calculate recurring dates in WPS Spreadsheets

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. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the month: In A1, enter any valid date in the month you want to calculate.
  3. 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. 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.
Calculate recurring weekday dates without creating a named function.Open and save Microsoft Excel XLSX files.Use familiar spreadsheet cells and formulas in a free office suite.
QA img-9

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.