How to calculate the end date based on the start date and term duration selected by the user?

This is a common question from Google Forms users who sell subscriptions. Your users want to know when theirs ends as soon as they choose a term. Working it out by hand means counting months from the start date, for every signup. Google Forms cannot show this while they fill in the form. With Formfacade, you can use the ADD function to calculate the end date from the start date and term and your users will see it as soon as they make their selection. Even better, the end date is saved with the signup, so you can sort your renewals in Google Sheets.

Let's start. Create your form and follow the steps below.

Let's take the example of a subscription form. The users select the start date and their preferred subscription term and the form should automatically display the end date based on this selection. We will use the ADD function to dynamically add the months from the subscription question to the start date entered by the user and calculate the end date.

How to calculate the end date?

  • On the page that opens, name your form, click Build form and click Proceed. It can take about a minute.
  • You will land on the form's "Preview" tab.
  • Click on "Edit" tab
The tabs Edit, Preview, Share and Responses, with Edit marked
  • Click on the + icon (Add field) in the toolbar on the right. A new question will be added.
  • Enter "Start date" as the question and select Date in the question type list
  • Click on the + icon again
  • Enter "Choose the number of months (subscription)" as the question and select Dropdown in the question type list
  • Type 3 in the first box, then click Add option and type 6. Click Add option again and type 12.
  • Click on the + icon again
  • Enter "End date" as the question and select Date in the question type list
The toolbar on the right of the Edit tab, with the + icon marked
Click on the End date question, then click on the ⚙️ icon.
The form in Formfacade, with the EDIT tab, the End date question and its gear icon marked
Field settings screen will be displayed. Select the "Answer" tab and write the formula to add subscription chosen to the start date.
The field settings panel with the Answer tab and the Calculate box marked

Number answer choices in the subscription question

The subscription is a dropdown question with three number answer choices: 3, 6 and 12. We can use this directly to add the months to the start date and calculate the end date.
You can add months to a date by specifying "months" as the term in the ADD function.
Syntax:
start_date.add(number_of_months, "months")
Note: To enter start_date and number_of_months, type @ in the calculate option and select the start date and subscription question from the list. If no list appears on your form, make your form here instead: name it, click Create form and click Proceed, close the Assign points dialog and add these questions. The @ list opens on a form made there.
Write the formula in the Calculate option for end date question as shown below. Click Save.
The Calculate box with the Start date field, .add, the subscription field and "months"

Text answer choices in the subscription question

If you want to use text answer choices such as 3 months, 6 months and 1 year for the subscription dropdown question, you will have to use the IFS function to assign the numerical values for each of the text answer choices. For example:
start_date.add(IFS(subscription == "3 months", 3, subscription == "6 months", 6, subscription == "1 year", 12, 1),"months")
Note: To enter start_date and subscription, type @ in the calculate option and select the start date and subscription question from the list.
You can use ADD and IFS function to dynamically calculate the end date as shown below.
The Calculate box with the Start date field, .add and an IFS formula that turns 3 months, 6 months and 1 year into 3, 6 and 12
As soon as the user selects the start date and the subscription, the form shows the end date. Until the start date is entered, the end date shows a date around 1970, because an empty date counts from 1970.
The Signup for a subscription form with a start date of 1 November 2026, a term of 3 months and an end date of 1 February 2027

Summary

If your users choose a start date and a term, Google Forms cannot show them the end date. With Formfacade, you can use the ADD function in the Calculate option and the end date will be displayed as soon as they make their selection. Create your form and complete the steps above. If you have any questions, write to support.

Last updated: