How to Get Next Day Of Week in Excel

Why This Task Matters in Excel

Whether you run payroll every Friday, generate sales forecasts each Monday, or prepare management reports for the first business day of every month, you eventually face the question, “From a given date, what is the next occurrence of a specific weekday?” Manually counting forward on a calendar is error-prone and time-consuming, especially when the date needs to update automatically whenever the source date changes.

In finance, analysts often need to project cash flows to the next Friday if the current valuation date falls mid-week. Supply-chain planners schedule shipments for the next Tuesday or Thursday to align with carrier availability. HR departments send onboarding emails on the next Monday after a hire date, while marketing teams schedule email campaigns for the next Wednesday to maximize open rates. All these scenarios hinge on accurately locating a future weekday.

Excel excels (pun intended) at date arithmetic because every date is stored as a serial number. That means you can add, subtract, divide, and compare dates just as you would numerical values. Pair that with functions such as WEEKDAY, CHOOSE, WORKDAY, and TODAY, and you have a powerful toolkit for time-based automation. Skipping this skill leaves you dependent on manual edits, introducing risk to forecasts, budgets, and project plans. Moreover, knowing how to calculate “next weekday” deepens your understanding of Excel’s date system, which connects directly to more advanced workflows like dynamic time-series dashboards, Power Query transformations, and data-modeling in Power Pivot. Mastering this task is therefore a pivotal milestone on the path from casual user to data-driven professional.

Best Excel Approach

The most flexible, widely compatible method is a single-cell formula built around the WEEKDAY function combined with MOD arithmetic. WEEKDAY translates any date into a numeric weekday code, while MOD handles the wrap-around when the next occurrence spills into the following week. This approach is fully dynamic, works in every modern Excel version (including Excel for Microsoft 365, Excel 2010, and even Google Sheets), and does not require external tool-packs.

Core logic:

1. Calculate the numeric difference between the target weekday code and the weekday code of the input date.
2. Adjust the difference so that a value of zero (meaning the input date is already the desired weekday) converts to seven, ensuring we always move forward at least one day.
3. Add the adjusted difference to the original date to get the next occurrence.

Syntax pattern:

=InputDate + MOD( TargetCode - WEEKDAY(InputDate) , 7 ) + 1

Where:

• InputDate – reference to the starting date (can be a cell such as A2 or TODAY()).
• TargetCode – integer 1 through 7 representing Sunday through Saturday if WEEKDAY’s default return type is used.

Alternative when you prefer to label weekdays by name:

=InputDate + MOD( MATCH(TargetName, ["Sun","Mon","Tue","Wed","Thu","Fri","Sat"],0) - WEEKDAY(InputDate), 7 ) + 1

This method shines when users enter weekday names (Mon, Tue, etc.) while keeping the formula fully automatic.

Parameters and Inputs

InputDate must be an Excel-recognised date, not text masquerading as a date. Verify by changing the cell format to “General”; a proper date will turn into a whole number (for example, 45123). If you must accept text input, wrap it in DATEVALUE.

TargetCode is an integer 1-7 that matches the return type used by WEEKDAY. WEEKDAY’s optional second argument (return_type) can change the numeric mapping. For instance, if you set return_type to 2, Monday maps to 1. Always keep TargetCode synchronized with return_type to avoid off-by-one mistakes.

Optional return_type (second argument of WEEKDAY) decides whether your week starts on Sunday (1) or Monday (2) or other configurations. Use return_type 2 (Monday = 1) for European standards and ISO-8601 compatibility.

Edge cases:

• If InputDate is blank, the formula returns a #VALUE! error; guard with IF or LET.
• If TargetCode is outside 1-7, Excel produces a #NUM! error; validate with DATA > Data Validation list or an IF clause.
• Leap year and month boundaries are automatically handled because Excel date serials increment seamlessly.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you have an invoice date in cell A2 and need the next Friday for payment scheduling.

Sample data:
A\2 = 12-Apr-2023 (Wednesday)

Steps:

1. In cell B2, enter the formula below.

=A2 + MOD(6 - WEEKDAY(A2), 7) + 1

Explanation: WEEKDAY(A2) returns 4 (Wednesday) using default mapping. 6 (Friday) minus 4 equals 2. MOD(2,7) is 2, so B2 becomes A2 + 2 + 1 = 15-Apr-2023, which is indeed the next Friday.

Why the +1? The classic formula many websites show is A2 + MOD(6 - WEEKDAY(A2), 7). That returns the next or same Friday. Adding 1 forces the result to move at least one day forward, preventing the “same day” issue—critical when the authorisation process must always occur in the future.

Variations:

• If invoices arrive daily and you need the next Monday, change 6 to 2 (Monday’s code) while retaining the rest.
• If you use return_type 2 so Monday maps to 1, set WEEKDAY(A2,2) and adjust the TargetCode accordingly.

Troubleshooting: If you see a five-digit number instead of a date, the cell format is General or Number. Simply set Format Cells > Date. If the formula returns a #VALUE! error, verify that A2 is a genuine date: type `=ISTEXT(`A2) and ensure it returns FALSE.

Example 2: Real-World Application

Imagine a logistics department scheduling shipments. Orders placed Monday through Saturday must be dispatched the next Tuesday or Thursday, whichever occurs first after the order date. Orders placed on Sunday get dispatched on the next Tuesday.

Data set (simplified):
[Orders!A2:A6] holds order dates; [Orders!B2:B6] will hold dispatch dates.

Dispatch logic: Find the next date that is either Tuesday or Thursday.

Create a helper table somewhere (say, [H1:H2]) listing the target weekdays:
H\1 = 3 (Tuesday)
H\2 = 5 (Thursday)

Now enter in B2:

=LET(
   orderDate, A2,
   tuesDiff, MOD(3 - WEEKDAY(orderDate), 7) + 1,
   thursDiff, MOD(5 - WEEKDAY(orderDate), 7) + 1,
   orderDate + MIN(tuesDiff, thursDiff)
)

Walkthrough: LET stores intermediate results for clarity and performance. For 01-Jul-2023 (Saturday), WEEKDAY returns 7. tuesDiff calculates as 3 − 7 → −4; MOD(−4,7) = 3; plus 1 = 4. thursDiff computes 5 − 7 → −2; MOD(−2,7) = 5; plus 1 = 6. MIN picks 4, so dispatch date becomes 05-Jul-2023 (Tuesday).

Business impact: This formula ensures shipments always align with carrier pick-up days, reducing warehouse congestion and missed pickups. It is fully dynamic; when an order date changes, the next valid dispatch day updates automatically.

Performance: With thousands of orders, LET reduces recalculation overhead by re-using the same WEEKDAY result, instead of computing it twice. This is noticeable when the sheet is part of a larger, volatile model that recalculates frequently.

Example 3: Advanced Technique

Advanced payroll systems often require the next business day, excluding holidays, but pinned to a specific weekday. Suppose salaries must be paid on the next Wednesday that is also a business day, excluding a custom holiday list [Holidays!A2:A20].

Data:

• Hire date in C2
• Holiday list dynamic range: [Holidays]

Goal: Return the next Wednesday that is not a weekend and not listed in Holidays.

Formula in D2:

=LET(
   startDate, C2,
   targetCode, 4,                    /* Wednesday using return_type 1 */
   initial, startDate + MOD(targetCode - WEEKDAY(startDate), 7) + 1,
   nextBizWed, WORKDAY(initial - 1, 1, Holidays),
   nextBizWed
)

Explanation:

1. initial locates the next (or same) Wednesday.
2. We subtract one day from initial and push it through WORKDAY with 1 day forward, effectively saying “give me the next business day starting from the Tuesday before initial.” If the initial Wednesday is a holiday, WORKDAY skips ahead to Thursday, but we cannot allow that. So we wrap everything in a loop-free array formula:

=LET(
  n, SEQUENCE(14,,1,1), /* examine two weeks ahead */
  cand, startDate + n,   /* candidate dates */
  ndx, XLOOKUP(1,
       (WEEKDAY(cand) = targetCode) *
       (COUNTIF(Holidays, cand) = 0),
       n, , 0),
  startDate + ndx
)

Here we use SEQUENCE and XLOOKUP to scan the next fourteen days, filtering those that match Wednesday and are not holidays. The first match is returned. This approach avoids iterative calculation and remains volatile-free, suitable for enterprise-grade models with tight audit requirements.

Tips and Best Practices

1. Store target weekday codes in named cells (e.g., cell [Config]::TargetWeekday) so business users can change the target without editing formulas.
2. Use LET in Excel 365 to break complex formulas into readable steps, boosting maintainability and speed.
3. Always align WEEKDAY’s return_type across your workbook to avoid mismatched codes. Document the chosen mapping in a hidden “Meta” sheet.
4. When interacting with Power Query, convert dates to serial numbers only at the last step. This prevents unintended type changes.
5. For high-traffic dashboards, cache TODAY() in a dedicated cell and refer to it, rather than calling TODAY() in hundreds of formulas; recalculation time drops dramatically.
6. Combine conditional formatting to highlight “overdue” tasks whose next weekday has already passed, providing at-a-glance insights for managers.

Common Mistakes to Avoid

1. Forgetting the +1 offset, causing the formula to return the same date instead of the next date. This leads to duplicate payroll runs or emails sent prematurely. Fix by appending +1 or by switching to “next or same” logic deliberately.
2. Mixing weekday code systems (return_type 1 versus 2) across formulas, resulting in inconsistent outputs. Create a single named constant or parameter table to standardise return_type.
3. Feeding text dates like \"2023-07-01\" without DATEVALUE, causing #VALUE! errors. Validate input with ISNUMBER or data validation lists.
4. Overlooking holidays when next weekday must also be a business day, which might breach labour contracts or vendor agreements. Integrate WORKDAY or a custom scan as shown in Example 3.
5. Hard-coding month or year boundaries, such as adding seven days and assuming the answer is in the same month. Excel dates roll over, but downstream formatting or text functions may not—always test edge cases like 31-Dec.

Alternative Methods

The MOD approach remains fastest and easiest, but if you must obey holiday calendars and custom weekends, combine it with WORKDAY.INTL or move logic to Power Query for batch processing. Switching between methods is straightforward: test outputs in a side-by-side comparison column before decommissioning the former approach.

FAQ

When should I use this approach?

Use the MOD + WEEKDAY formula whenever you need a lightweight, single-cell solution that dynamically updates and where holiday exclusion is not essential—or can be handled separately.

Can this work across multiple sheets?

Yes. Reference InputDate on Sheet1 and place the formula on Sheet2: =Sheet1!A2 + MOD( TargetCode - WEEKDAY(Sheet1!A2), 7 ) + 1. Always qualify sheet names to avoid accidental pointer shifts when sheets are moved.

What are the limitations?

The base formula does not exclude holidays or variable-length weekends. It assumes a standard seven-day week. For complex calendars, augment with WORKDAY.INTL or a holiday scan loop as illustrated earlier.

How do I handle errors?

Wrap with IFERROR to catch invalid inputs: =IFERROR( InputDate + MOD(...) + 1, "Invalid date" ). Use ISNUMBER to pre-validate that InputDate is a serial date, and use Data Validation lists to restrict weekday codes.

Does this work in older Excel versions?

Absolutely. The MOD + WEEKDAY method runs in Excel 2003 onward. LET, SEQUENCE, and XLOOKUP require Microsoft 365 or Excel 2021. For legacy environments, replicate LET with helper cells, and replace SEQUENCE with a manual array entered using Ctrl+Shift+Enter.

What about performance with large datasets?

One million rows recalculate quickly because WEEKDAY and MOD are lightweight. Performance bottlenecks usually arise from volatile functions like TODAY() appearing in every row. Cache TODAY() in a single cell, and point formulas to that cell. Use LET to avoid duplicate WEEKDAY calls.

Conclusion

Knowing how to calculate the next occurrence of a weekday empowers you to build dynamic schedules, automate notifications, and maintain compliance with cut-off dates—without manual edits. The core MOD + WEEKDAY technique is simple yet robust, and it integrates seamlessly with more advanced tools such as WORKDAY.INTL, Power Query, and even VBA when special calendars are needed. Practice the examples, adapt them to your industry, and you will unlock faster, safer workflows that stand the test of time. Keep exploring related date functions, and soon you will orchestrate complete time-driven solutions across your entire Excel environment.