WPS Spreadsheets: Calculate the Next Subscription Renewal Date
Question details
Calculate the next renewal date for monthly, biannual, or annual subscriptions while keeping the original subscription start date unchanged.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet stores the subscription start date in column D and the plan type in column F, and needs the first renewal date that occurs after today.
- Observed behavior
- The result should advance by the plan interval until it returns a date later than the current date.
Make sure the start-date cells contain real spreadsheet dates rather than text, and use consistent plan labels such as Annual, Biannual, and Monthly. Format the result column as a Date before entering the formula.
Use one formula for annual, biannual, and monthly renewal dates
Use this option when the start date is in D2 and the plan type is in F2. It calculates the first renewal date strictly after today without changing the original start date.
The formula converts the plan label into a number of months, then uses EDATE to move forward by complete billing intervals. If the start date is already in the future, it returns that start date instead.
Click the cell where you want the next renewal date to appear, such as G2.
Type or paste this formula into G2: =LET(start,D2,months,SWITCH(F2,"Annual",12,"Biannual",6,"Monthly",1),IF(start>TODAY(),start,EDATE(start,months*(QUOTIENT(DATEDIF(start,TODAY(),"m"),months)+1))))
Press Enter, then select the result cell and use Home > Number Format > Date if the value is displayed as a serial number.
Drag the fill handle from the lower-right corner of G2 down the column to calculate renewal dates for additional subscriptions.
Use a helper column for the renewal interval in months
Use this option if you prefer to store the billing interval as a numeric value, which makes the formula easier to maintain when more plan types are added.
Calculate recurring subscription renewal dates with WPS Spreadsheets
WPS Spreadsheets lets you build renewal-date formulas with functions such as TODAY, EDATE, DATEDIF, LET, and SWITCH. Enter the formula once, copy it down your subscription list, and let the next renewal date update automatically as time passes.
- 1. Open the subscription worksheet: Open WPS Spreadsheets and select File > Open to open your existing subscription workbook or create a new sheet.
- 2. Set up the source columns: Enter the original start date in column D and the plan type, such as Annual, Biannual, or Monthly, in column F.
- 3. Add the renewal formula: Select a result cell such as G2, enter the renewal-date formula, and press Enter to calculate the next date after today.
- 4. Copy and format the results: Drag the fill handle down the result column, then select Home > Number Format > Date to format all calculated renewal dates.
Frequently Asked Questions
Why does the formula return the start date for a future subscription?
The IF(start>TODAY(),start,...) portion intentionally returns the original start date when it is later than today, because that is already the next upcoming renewal-related date.
How can I calculate a quarterly renewal date instead?
Add a quarterly plan label to the SWITCH function with a 3-month interval, for example: SWITCH(F2,"Annual",12,"Biannual",6,"Quarterly",3,"Monthly",1).
What happens when today is exactly a renewal date?
The formula returns the following renewal date, not today's date. For example, a monthly plan due today advances to the next monthly renewal because the result must be later than TODAY().
Why is my renewal result shown as a number instead of a date?
Select the result cell or column, then choose Home > Number Format > Date in WPS Spreadsheets. Spreadsheet dates are stored as numbers until a date format is applied.
Can I use different plan names in the formula?
Yes. Replace the text values in SWITCH, such as "Annual" and "Biannual", with the exact labels used in your plan-type cells. The text must match the cell value.
