Get the Next Renewal Date Based on Custom Month Intervals in Google Sheets

Published on

You can use a formula or a custom named function to calculate the Next Renewal Date in Google Sheets based on a specified number of months for a subscription model.

Let’s say you’ve issued maintenance contracts to various parties, each with its own renewal interval.

In your spreadsheet:

  • Column A is titled “Contract Signed Date” and contains the start dates of each maintenance contract.
  • Column B is titled “Renewal Period in Months,” where you specify the auto-renewal interval (e.g., 1, 3, 6, or 12 months).

For example, if a contract was signed on 28-Feb-2020 and it renews every 12 months, and today is 09-Dec-2022, the next renewal date should be 28-Feb-2023.

Next Renewal Date: Sample Data, Formula, and Explanation

In this example:

  • Column A: Contract Signed Dates
  • Column B: Custom Month Intervals
  • Column C: Resulting Next Renewal Dates
Example of calculating the next renewal date in Google Sheets using various month intervals

The formula to calculate the Next Renewal Date in Google Sheets is placed in cell C2. You can either drag it down manually or use an ArrayFormula to apply it across the entire column.

Master Formula:

=LET(
   start, A2,
   interval, B2,
   monthsPassed, DATEDIF(start, TODAY(), "m"),
   nextRenewal, EDATE(start, interval * (INT(monthsPassed / interval) + 1)),
   TO_DATE(IFERROR(nextRenewal, start))
)

Apply Formula to Entire Column:

To calculate the Next Renewal Date in Google Sheets for multiple rows at once, enter the following formula in cell C2:

=ArrayFormula(
   LET(
      start, A2:A,
      interval, B2:B,
      monthsPassed, DATEDIF(start, TODAY(), "m"),
      nextRenewal, EDATE(start, interval * (INT(monthsPassed / interval) + 1)),
      TO_DATE(IFERROR(nextRenewal, start))
   )
)

Formula Logic and Explanation

Let’s break it down:

  • DATEDIF(start, TODAY(), "m"): Calculates how many complete months have passed since the contract started.
  • INT(monthsPassed / interval) + 1: Determines how many full renewal cycles have passed, then adds 1 to get the next upcoming one.
  • EDATE(start, interval * ...): Projects the next renewal date forward based on the start date and number of months.
  • TO_DATE(...): Formats the result as a date.
  • IFERROR(..., A2): Returns the start date if an error occurs (e.g., empty or invalid input).

This approach ensures your renewal dates are dynamic and auto-updating based on the current date.

Custom Named Function to Get the Next Renewal Date

To make things easier, you can convert this into a Named Function.

Syntax:

NEXT_RENEWAL_DATE(startDates, intervals)
  • startDates – The cell or array with contract/subscription start dates.
  • intervals – The cell or array with renewal periods (in months).

Examples:

  1. A single subscription starting on 08-Nov-2021 with a 6-month interval:
=NEXT_RENEWAL_DATE(DATE(2021, 11 , 8), 6)
  1. Referencing cells A2 and B2:
=NEXT_RENEWAL_DATE(A2, B2)
  1. For a full range:
=NEXT_RENEWAL_DATE(A2:A10, B2:B10)
Example of using the custom function NEXT_RENEWAL_DATE() in Google Sheets to calculate upcoming renewal dates

To import this named function, make a copy of the following sample sheet and use the Named Function import option in Google Sheets:

Sample Sheet

Conclusion

This method is ideal for dynamically calculating the Next Renewal Date in Google Sheets based on flexible subscription models or contract intervals. Whether you’re managing subscriptions, maintenance contracts, or policy renewals, this approach ensures your dates are always up to date—automatically.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

More like this

Calculate Trip Days by Month (Start, End, and Full Days) in Google Sheets

If you're managing business travel in Google Sheets, you may need to calculate how...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.