Calculate Next Visit Dates in Excel from Text Intervals
Question details
Create a formula that returns the next visit date based on a text interval and a starting date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The interval text is entered in cell B2 and the starting visit date is entered in cell C2.
- Observed behavior
- The worksheet should add the correct number of days or months for Weekly, Biweekly, Monthly, Quarterly, Semiannually, and Yearly intervals.
Make sure C2 contains a real Excel date rather than date text, and use consistent interval names in B2. The formula matches the text exactly, so use “Biweekly” for every two weeks.
Use IFS and EDATE to calculate the next visit date
Use this formula when B2 contains a supported interval name and C2 contains the current or starting visit date.
IFS selects the correct calculation for each interval. Adding 7 or 14 handles weekly schedules, while EDATE safely moves dates by whole months for monthly, quarterly, semiannual, and yearly schedules.
In cell B2, enter an interval such as Weekly, Biweekly, Monthly, Quarterly, Semiannually, or Yearly. In cell C2, enter the starting visit date.
Select the result cell, such as D2, and enter: =IFS(B2="Weekly",C2+7,B2="Biweekly",C2+14,B2="Monthly",EDATE(C2,1),B2="Quarterly",EDATE(C2,3),B2="Semiannually",EDATE(C2,6),B2="Yearly",EDATE(C2,12),TRUE,"")
Press Enter, then select the result cell and use Home > Number Format > Short Date or Long Date to display the calculated date.
Drag the fill handle from the lower-right corner of D2 downward to apply the formula to additional visit rows.

Calculate recurring visit dates with formulas in WPS Spreadsheets
WPS Spreadsheets supports IFS, EDATE, date formatting, and fill handles, so you can convert schedule labels into accurate next visit dates in the same way you would in Excel.
- 1. Create interval and date columns: Open WPS Spreadsheets and enter the interval text in column B and the starting date in column C.
- 2. Select the next-date cell: Click the first result cell, such as D2, where the next visit date should appear.
- 3. Insert the date formula: Type =IFS(B2="Weekly",C2+7,B2="Biweekly",C2+14,B2="Monthly",EDATE(C2,1),B2="Quarterly",EDATE(C2,3),B2="Semiannually",EDATE(C2,6),B2="Yearly",EDATE(C2,12),TRUE,"") and press Enter.
- 4. Apply a date format: Select the calculated cells, then choose Home > Number Format and select a date format.
- 5. Extend the formula: Drag the cell fill handle down to calculate next visit dates for the remaining rows.

Frequently Asked Questions
Why should I use EDATE instead of adding 30 days for a monthly visit?
Use EDATE(C2,1) because it advances the calendar by one month. Adding 30 days can return the wrong date for months with 28, 29, or 31 days.
What happens if the interval in B2 is not listed in the formula?
The final TRUE,"" condition returns a blank cell. Add another IFS condition before TRUE if you need to support another interval name.
Can I use this formula for every two months?
Yes. Add B2="Every 2 Months",EDATE(C2,2) before the final TRUE,"" condition. This is clearer than using “Bimonthly,” which can have different meanings.
Why does the formula return a number instead of a date?
The calculation is likely correct, but the result cell is using General formatting. Select the cell and choose Home > Number Format > Short Date.




