Explanation
The first part of the solution uses the MIN and TODAY functions to find the “next date” based on the date today. This is done by filtering the dates through the IF function:
IF((date>=TODAY()),date)
The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today:
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
When a result is TRUE, the date is passed into array returned by IF. When a result is FALSE, the date is replaced by the boolean FALSE. The IF function returns the following array to MIN:
{FALSE;FALSE;FALSE;43371;43385;43399;43413;43427;43441;43455}
The MIN function then ignores the FALSE values, and returns the smallest date value (43371), which is the date Sept. 28, 2018 in Excel’s date system.
Getting the movie name
To display the movie associated with the “next date”", we use INDEX and MATCH:
=INDEX(movie,MATCH(G6,date,0))
Inside INDEX, MATCH finds the position of the date in G6 in the list of dates. This position, 4 in the example, is returned to INDEX as a row number:
=INDEX(movie,4)
and INDEX returns the movie at that position, “The Dark Knight”.
All in one formula
To return the next Movie in a single formula, you can use this array formula:
{=INDEX(movie,MATCH(MIN(IF((date>=TODAY()),date)),date,0))}
With MINIFS
If you have a newer version of Excel, you can use the MINIFS function instead of the array formula in G6:
=MINIFS(date,date,">="&TODAY())
MINIFS was introduced in Excel 2016 via Office 365.
Handling errors
The formula on this page will work even when events aren’t sorted by date. However, if there are no upcoming dates , the MIN function will return zero instead of an error. This will display as the date “0-Jan-00” in G6, and the INDEX and MATCH formula will throw an #N/A error, since there is no zero-th row to get a value from. To trap this error, you can replace MIN with the SMALL function, then wrap the whole formula in IFERROR like this:
={IFERROR(SMALL(IF((date>=TODAY()),date),1),"None found")}
Unlike MIN, the SMALL function will throw an error when a value isn’t found, so IFERROR can be used to manage the error.
Explanation
This example demonstrates how to calculate the nth occurrence of a specific day of the week within a month. For instance, you might need to find the 2nd Tuesday, the 4th Wednesday, or the 1st Friday of any given month. The worksheet is structured with the starting date in column B, the target day of week (dow) as a number in column C, and the occurrence number (n) in column D. Note that the date is assumed to be the first day of the month. The article below explains in detail how the formula in the worksheet shown works. It also shows how to rewrite the formula with the LET function to improve transparency and to trap potential problems.
This example shows how to get one specific day of the week in a month. If you instead want to list all nth weekdays in a given date range, see the example on this page .
- The WEEKDAY function
- How the core formula works
- Using LET for clarity
- Normalizing to the first of the month
- Guarding against non-existent dates
The WEEKDAY function
The formulas on this page depend on the WEEKDAY function , so it is important to understand how it works. When given a date, WEEKDAY returns a number representing the day of the week for a given date. By default, WEEKDAY returns numbers 1 through 7, where 1 represents Sunday, and 7 represents Saturday:
| Day | Number |
|---|---|
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
For example, =WEEKDAY(“6-Dec-2025”) returns 7, because December 6th, 2025, is a Saturday, and =WEEKDAY(“7-Dec-2025”) returns 1, because December 7th, 2025, is a Sunday. We use the WEEKDAY function in the formulas below to figure out what day of week a particular date falls on. We also use the same numeric codes above to specify the target day of week (dow) that we want to find. For example, if the target day of week is Tuesday, we would use the number 3, since Tuesday is the 3rd day of the week in WEEKDAY’s default numbering scheme.
How the core formula works
At a high level, the formula starts on the first day of the month, calculates an offset to reach the first target day, then adds the appropriate number of complete weeks to arrive at the nth occurrence of the target. The start date is in cell B5, the target day is in cell C5, and the value for n comes from cell D5. The formula in E5 looks like this:
=B5+MOD(C5-WEEKDAY(B5),7)+(D5-1)*7
First, we calculate the offset to the target day of week using the MOD function and the WEEKDAY function like this:
MOD(C5-WEEKDAY(B5),7)
This is the trickiest part of the formula to understand. The MOD function is used here to calculate a repeating pattern of numbers (0-6), similar to how a clock wraps around from 12 back to 1. By taking the difference between the target day of week (C5) and the current day of week (WEEKDAY(B5)), then wrapping that difference with MOD, we get the number of days needed to move forward to reach the target day. For example, if the starting date is a Monday (2) and we want Tuesday (3), the offset is 1 day. If the starting date is a Thursday (5) and we want Tuesday (3), MOD wraps the calculation to give us 4 days forward to the next Tuesday.
Once we have the offset, we add it to the starting date in B5 to get the first occurrence of the target day of the week:
B5+MOD(C5-WEEKDAY(B5),7)
This moves us from the given starting date to the first occurrence of the target day of the week on or after that date. The final step is to add the appropriate number of complete weeks to reach the nth occurrence, which is done by multiplying the difference between the nth occurrence and 1 by 7:
+(D5-1)*7
Since we’ve already landed on the first occurrence, we add (n-1) complete weeks to reach the desired occurrence. For the 1st occurrence, we add zero weeks. For the 2nd occurrence, we add 1 week (7 days), and so on.
Using LET for clarity
The formula above can be rewritten using the LET function to make the logic more transparent with named variables like this:
=LET(
date, B5,
dow, C5,
n, D5,
offset, MOD(dow-WEEKDAY(date),7),
date + offset + (n-1)*7
)
Use the keyboard shortcut Control + Shift + U to expand the formula bar.
This version makes the formula self-documenting. Each step has a clear name: date is the starting date, dow is the target day of week, n is the occurrence we want, and offset is the calculated adjustment needed to reach the target day. The final line is the formula we saw earlier, but now it is clear what each part does:
date + offset + (n-1)*7
Normalizing to the first of the month
The formula above assumes that the date provided is always the first of a month, but it doesn’t verify this requirement. If you provide a date that is not the first, the formula may return an incorrect result. To trap and prevent this potential problem, we can extend the LET version to normalize any incoming date to the first of the month, like this:
=LET(
date, B5,
dow, C5,
n, D5,
first, EOMONTH(date,-1)+1,
offset, MOD(dow-WEEKDAY(first),7),
first + offset + (n-1)*7
)
The variable first calculates the first day of the month using EOMONTH, which returns the last day of the previous month, plus one day. This ensures the formula always starts from the beginning of the month, regardless of what date is provided.
Guarding against non-existent dates
Another problem with the formula above is that it does not guard against cases where the nth occurrence doesn’t exist in a given month. For example, asking for the 5th Tuesday in a month that only has 4 Tuesdays will cause the formula to return an incorrect date in the following month. To prevent this, you can add a check that returns an error when the result falls outside the target month:
=LET(
date, B5,
dow, C5,
n, D5,
first, EOMONTH(date,-1)+1,
offset, MOD(dow-WEEKDAY(first),7),
result, first + offset + (n-1)*7,
IF(MONTH(result)=MONTH(date), result, NA())
)
The final IF statement compares the month of the calculated result with the month of the original date. If they match, the result is valid and is returned. If they don’t match, it means we have landed on a date in the next month, and the formula returns a #N/A error with the NA function to make it clear that the requested occurrence doesn’t exist in that month. If you prefer not to see an error, you could return a custom message or an empty string ("") instead.
Note: The LET versions of the formula return the same result as the original formula; they just do so in a more transparent way. The last two formulas add functionality.
