Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the next occurrence of a weekday in Excel and Google Sheets
Excel & Google Sheets
=A2+MOD(B2-WEEKDAY(A2,2),7)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put the reference date in column A and the target weekday number (1=Mon … 7=Sun, ISO) in column B.
- 2In C2 enter =A2+MOD(B2-WEEKDAY(A2,2),7).
- 3If MOD returns 0 the date is already that weekday; to force the NEXT occurrence add: =A2+MOD(B2-WEEKDAY(A2,2)-1,7)+1.
- 4Format column C as Date.
Tips
- To always find the NEXT occurrence (not today), wrap with +1: =A2+MOD(B2-WEEKDAY(A2,2)-1,7)+1.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What numbers correspond to which days?
With WEEKDAY mode 2: 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday.
Can I hard-code the day instead of using a cell reference?
Yes. To always find the next Friday (5), use =A2+MOD(5-WEEKDAY(A2,2),7).