How to calculate the days between two dates?

This is a common question from Google Forms users who work with two dates. For example, if you run a leave request form, you need the number of days for each request. When people work it out themselves, they count the days differently or simply get it wrong. Google Forms only records the two dates, so you have to calculate the days later in a sheet. With Formfacade, you can subtract the start date from the end date and your users will see the number of days as soon as they enter the dates. Even better, your team gets that number in Google Sheets without writing a formula there.

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

Let's take the example of a leave request form. When the user enters the leave start date and leave end date, we can automatically calculate the number of days by writing a formula in the Calculate option for the number of days question.

How to calculate the number of days?

  • 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 "Leave From" as the question and select Date in the question type list
  • Click on the + icon again
  • Enter "Leave Till" as the question and select Date in the question type list
  • Click on the + icon again
  • Enter "Number of days" as the question. It is already a Short answer.
The toolbar on the right of the Edit tab, with the + icon marked
Click on the ⚙️ icon at the bottom right of the Number of days question.
The form in Formfacade, with the EDIT tab, the Number of days question and the gear icon at the bottom right of its card marked
Field settings screen will be displayed. Select the "Answer" tab and write the formula to calculate the number of days.
The field settings panel with the Answer tab and the Calculate box marked

Number of days between two dates

When you subtract the start date from the end date, the result will be in milliseconds. You can divide the answer by 24 * 60 * 60 * 1000 to convert it into number of days. Please note that you must add 1 to the results to include the start date.
((Leave Till - Leave From) / (24 * 60 * 60 * 1000)) + 1
  • Divide by 1000 to convert into seconds
  • Divide by (60 * 1000) to convert into minutes
  • Divide by (60 * 60 * 1000) to convert into hours
  • Divide by (24 * 60 * 60 * 1000) to convert into days
Write the formula in the Calculate option as shown below. Click Save.
The Answer tab with ((Leave Till - Leave From) / (24 * 60 * 60 * 1000)) + 1 in the Calculate box
The default value for the date field is 1970-01-01. Until the user fills both dates, the formula above shows a wrong number such as -20758.96. You can use this default value to check if the user has entered the value for the date fields, with this formula. Click Save.
IFS(Leave Till != "1970-01-01", ((Leave Till - Leave From) / (24 * 60 * 60 * 1000)) + 1, 0)
The Calculate box with an IFS formula that checks Leave Till is not 1970-01-01 before calculating the days
Once both dates are entered, the form shows the number of days. For example, 2 November to 6 November gives 5.
The Time off request form with Leave From 2 November, Leave Till 6 November and Number of days 5

Summary

If you need the number of days between two dates, Google Forms cannot calculate it. With Formfacade, you can subtract the start date from the end date in the Calculate option and the number of days will be displayed as soon as the user enters the dates. Create your form and complete the steps above. If you have any questions, write to support.

Last updated: