Date functions

This is a common question from Google Forms users who work with dates, such as a due date, an age or the days between two dates. Google Forms can collect a date, but it cannot calculate one. You end up asking people to work it out or you do it later in a sheet. Formfacade provides built-in date functions that update today's date and add or subtract dates, so your users see the result while they fill in the form. Even better, that result is saved with the response, so it reaches your Google Sheets too.

Let's start. Create your form and write these functions in the Calculate option of a question.

Here's the list of date functions:

DATE

Date function can be used to convert a year, month and day into a date.
Syntax:
DATE(year, month, day)
where
  • year - the year component of the date
  • month - the month component of the date
  • day - the day component of the date
Example usage:
DATE(2020, 9, 25)
DATE(2020, 12, 12)

TODAY

Today function returns the current date. Syntax: TODAY()

NOW

NOW function returns the current date and time. Syntax: NOW()

ADD

Add function can be used to add seconds, minutes, hours, days, months or years to a date or date & time question.
Syntax:
inputdate.add(number, term)
where
  • inputdate - the date to consider in the calculation. This can be date or date & time question or a formula that returns a date or date & time
  • number - a number value that must be added to the date or date & time
  • term - determines whether to add seconds, minutes, hours, days, months or years
Example usage:
TODAY().add(3,"days")
TODAY().add(6,"months")
TODAY().add(2,"years")
NOW().add(3,"hours")
NOW().add(25,"minutes")
NOW().add(10,"seconds")

SUBTRACT

Subtract function can be used to subtract seconds, minutes, hours, days, months or years from a date or date & time question.
Syntax:
inputdate.subtract(number, term)
where
  • inputdate - the date to consider in the calculation. This can be date or date & time question or a formula that returns a date or date & time
  • number - a number value that must be subtracted from the date or date & time
  • term - determines whether to subtract seconds, minutes, hours, days, months or years
Example usage:
TODAY().subtract(3,"days")
TODAY().subtract(6,"months")
TODAY().subtract(2,"years")
NOW().subtract(3,"hours")
NOW().subtract(25,"minutes")
NOW().subtract(10,"seconds")

DIFF

Diff function calculates the number of seconds, minutes, hours, days, months or years between two date or date & time questions.
Syntax:
enddate.diff(startdate, unit)
where
  • enddate - The end date to consider in the calculation. This can be date or date & time question or a formula that returns a date or date & time.
  • startdate - The start date to consider in the calculation. This can be date or date & time question or a formula that returns a date or date & time.
  • unit - determines the unit of time for the result. The unit can be seconds, minutes, hours, days, months or years.
Example usage:
TODAY().diff(DATE(1950,12,12),"years")
TODAY().diff(DATE(2020,06,18),"months")
TODAY().diff(DATE(2020,09,27),"days")

Summary

If you need dates calculated in your form, Google Forms cannot do it. With Formfacade, you can use functions such as DATE, TODAY, NOW, ADD, SUBTRACT and DIFF. Create your form and write these functions in the Calculate option of a question.

Last updated: