How to Calculate the Next Tuesday and Thursday Using Excel Formulas
Question details
The user needs to formulate two cells to dynamically display the next occurring Tuesday and Thursday based on a starting reference date, with the dates alternating in sequence.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic schedule or tracking sheet that requires calculating the immediate next applicable weekday (Tuesday or Thursday) and the subsequent one based on an inputted date.
- Observed behavior
- The target cells must continually alternate to show the correct upcoming Tuesday and Thursday dates in chronological order.
Ensure that your reference cell containing the starting date is properly formatted as a Date. If formatted as General or Text, the formulas may return standard serial numbers instead of recognizable calendar dates.
Use WEEKDAY and LOOKUP Formulas
This method accurately calculates the upcoming dates by combining the WEEKDAY function to determine the current day with a LOOKUP array to pinpoint the next Tuesday and Thursday.
This is the most reliable approach for creating alternating date schedules. The first formula identifies the next immediate Tuesday or Thursday, while the second formula checks the result of the first cell to determine the subsequent date.
Type your reference date into cell C1 and format it as a Date.
Select cell A1 and enter the formula: =C1-WEEKDAY(C1,2)+LOOKUP(WEEKDAY(C1,2),{1,3,5},{2,5,9}). Press Enter.
Select cell B1 and enter the formula: =IF(WEEKDAY(A1,2)=2,A1+2,A1+5). Press Enter.
Use Advanced WEEKDAY Return Types
An alternative approach leveraging Excel's specific return types (13 for Wednesday start, 15 for Friday start) to dynamically shift the week calculations.
Master Date Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, including WEEKDAY, LOOKUP, and IF statements. You can seamlessly calculate alternating schedules and build complex date tracking tools in a highly intuitive interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet document.
- 2. Prepare your dates: Enter your starting date in cell C1 and ensure it is formatted as a standard Date.
- 3. Access the function builder: Select your target cell and click the 'fx' button to search for the WEEKDAY function if you need syntax assistance.
- 4. Input the calculation: Paste the provided formula combinations directly into the formula bar.
- 5. Apply across rows: Press Enter and use the Fill Handle to drag the formula down and apply the alternating logic to a full schedule.
Frequently Asked Questions
Why does my date formula return a 5-digit number like 44299?
This happens when the resulting cell is formatted as 'General' or 'Number'. Spreadsheet software stores dates as sequential serial numbers. To view it properly, right-click the cell, select 'Format Cells', and choose 'Date'.
Can I adjust the formula to find Monday and Wednesday instead?
Yes. You will need to adjust the numerical arrays in the LOOKUP function or change the return_type in the WEEKDAY function to match the index of the specific days you want to target (e.g., 1 for Monday, 3 for Wednesday depending on the week's start day).
What does the '2' mean in WEEKDAY(C1, 2)?
The '2' is the return_type argument. It configures the function to treat Monday as the first day of the week (1) and Sunday as the seventh day (7), which aligns perfectly with standard international business weeks.
