Function Problems

Calculate Next Visit Dates in Excel from Text Intervals

Camila MilosovichCamila Milosovich Nov 12, 2026 869 views

Question details

Create a formula that returns the next visit date based on a text interval and a starting date.

Calculate Next Visit Dates in Excel from Text Intervals
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the interval and start date

In cell B2, enter an interval such as Weekly, Biweekly, Monthly, Quarterly, Semiannually, or Yearly. In cell C2, enter the starting visit date.

2
Enter the formula

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,"")

3
Confirm and format the result

Press Enter, then select the result cell and use Home > Number Format > Short Date or Long Date to display the calculated date.

4
Copy the formula for other records

Drag the fill handle from the lower-right corner of D2 downward to apply the formula to additional visit rows.

Use IFS and EDATE to calculate the next visit date
Use clear interval terms: Biweekly normally means every two weeks. Avoid using “Bimonthly” unless you define whether it means twice per month or every two months.
Calculate scheduled visit dates in WPS Spreadsheets

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. 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. 2. Select the next-date cell: Click the first result cell, such as D2, where the next visit date should appear.
  3. 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. 4. Apply a date format: Select the calculated cells, then choose Home > Number Format and select a date format.
  5. 5. Extend the formula: Drag the cell fill handle down to calculate next visit dates for the remaining rows.
Use IFS and EDATE to calculate weekly, monthly, quarterly, and yearly follow-up dates.Fill formulas down a schedule table quickly with the fill handle.Open, edit, and save Microsoft Excel .xlsx and .xls files with high format compatibility.Use a lightweight, familiar spreadsheet interface for recurring appointment tracking.
microsoft office alternative - wps office

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.