How to Calculate the Next Tuesday or Meeting Date in Excel
Question details
The user needs a formula to calculate the next occurrence of a designated weekday (such as a meeting day stored in a named range) based on a given appointment date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Scheduling future meetings or calculating the exact date of a designated weekday based on an existing reference date.
- Observed behavior
- The formula correctly returns the date of the next designated weekday. If the current appointment date falls exactly on the meeting day, the formula returns the same weekday in the following week (exactly 7 days later).
Ensure you have defined a named range for your weekdays (e.g., 'GSMDays') and verify that the target cells are formatted as Dates so the calculated numbers display correctly as calendar dates.
Calculate the Next Meeting Date in a Regular Data Range
Use this IF, WEEKDAY, and MATCH combined formula when your data is organized in standard spreadsheet cells.
This formula uses the WEEKDAY function to determine the day of the week for your current appointment and MATCH to find your target meeting day in the 'GSMDays' named range.
The logic checks if the current weekday has already passed the target meeting day. If the appointment occurs on the meeting day itself, the formula automatically adds 7 days to push it to the next week.
Click on the cell where you want the next meeting date to appear (for example, cell G6).
Type the following formula: =IF(WEEKDAY(F6,2)>=MATCH($G$4,GSMDays,0),F6+7-ABS(WEEKDAY(F6,2)-MATCH($G$4,GSMDays,0)),F6+ABS(WEEKDAY(F6,2)-MATCH($G$4,GSMDays,0)))
Press Enter to get the result. Then, click and drag the fill handle at the bottom-right corner of the cell downwards to fill the formula for the rest of your data list.
Calculate the Next Meeting Date within an Excel Table
Use this MOD-based formula when working with structured Excel Tables, which automatically adjust when new rows are added.
Easily Calculate Next Meeting Dates with WPS Office
WPS Spreadsheet fully supports complex date calculations, structured table references, and named ranges. You can manage employee schedules, meeting dates, and project timelines effortlessly with 100% compatibility with Microsoft Excel formulas.
- 1. Open your schedule document: Launch WPS Spreadsheet and open your scheduling workbook.
- 2. Define the named range: Go to the Formulas tab, select Name Manager, and define your meeting days as 'GSMDays'.
- 3. Enter the formula: Paste the IF or MOD formula directly into your target cell, just as you would in Microsoft Excel.
- 4. Format the output as Date: Right-click the result cells, select Format Cells, and choose Date to display your calculated meeting schedules correctly.
Frequently Asked Questions
How do I create the 'GSMDays' named range?
Select the cells containing your list of weekdays. Then, go to the Formulas tab, click on 'Define Name' (or 'Name Manager'), type 'GSMDays' into the Name box, and click OK. This allows the MATCH function to search through your specific list of days.
Why is my formula returning a 5-digit number instead of a date?
Spreadsheet software stores dates as sequential serial numbers. If your formula returns a number like 44927, right-click the cell, select 'Format Cells', navigate to the 'Number' tab, and choose 'Date'. The number will instantly convert into a readable calendar format.
Does this formula work if my weekend days are different?
Yes. By using WEEKDAY(F6, 2), the formula sets Monday as the first day of the week (1) and Sunday as the seventh day (7). If your organizational week starts on a different day, you can adjust the 'return_type' argument (the number 2) in the WEEKDAY function to match your needs.
