Chapter 4 · Formulas
Date functions
Formfacade's date functions DATE, TODAY, NOW, ADD, SUBTRACT and DIFF, with formulas for age, days between two dates, end dates, and more.
Quick Answer
How do I calculate dates in Google Forms?
Google Forms can collect a date but can't calculate one. In Formfacade, write a date function such as TODAY(), ADD, SUBTRACT, or DIFF in the Calculate option of a question. People see the result while they fill in the form, and it's saved with the response.
Write these formulas in the Calculate option of a question, as described in Calculated fields. Put a formula that returns a date in a Date question.
| Function | Syntax | Returns | Example |
|---|---|---|---|
| DATE | DATE(year, month, day) | A date from a year, month and day | DATE(2020, 9, 25) |
| TODAY | TODAY() | The current date | TODAY() |
| NOW | NOW() | The current date and time | NOW() |
| ADD | inputdate.add(number, term) | The date with seconds, minutes, hours, days, months or years added | TODAY().add(3,"days") |
| SUBTRACT | inputdate.subtract(number, term) | The date with seconds, minutes, hours, days, months or years subtracted | TODAY().subtract(6,"months") |
| DIFF | enddate.diff(startdate, unit) | The number of seconds, minutes, hours, days, months or years between two dates | TODAY().diff(DATE(1950,12,12),"years") |
DATE
Converts a year, month and day into a date.
DATE(year, month, day)
year: the year component of the date.month: the month component of the date.day: the day component of the date.
DATE(2020, 9, 25)
DATE(2020, 12, 12)
TODAY and NOW
TODAY() returns the current date. NOW() returns the current date and time.
TODAY()
NOW()
ADD
Adds seconds, minutes, hours, days, months or years to a date or date & time.
inputdate.add(number, term)
inputdate: the date to use. This can be a date or date & time question, or a formula that returns a date or date & time.number: the number to add.term:"seconds","minutes","hours","days","months"or"years".
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
Subtracts seconds, minutes, hours, days, months or years from a date or date & time.
inputdate.subtract(number, term)
inputdate: the date to use. This can be a date or date & time question, or a formula that returns a date or date & time.number: the number to subtract.term:"seconds","minutes","hours","days","months"or"years".
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
Calculates the number of seconds, minutes, hours, days, months or years between two dates or date & time values.
enddate.diff(startdate, unit)
enddate: the end date. This can be a date or date & time question, or a formula that returns a date or date & time.startdate: the start date, in the same forms asenddate.unit: the unit of the result:"seconds","minutes","hours","days","months"or"years".
TODAY().diff(DATE(1950,12,12),"years")
TODAY().diff(DATE(2020,06,18),"months")
TODAY().diff(DATE(2020,09,27),"days")
Parts of a date
| Function | Returns | Example |
|---|---|---|
year() | The year | TODAY().year() |
month() | The month | TODAY().month() |
date() or getDate() | The day of the month | TODAY().date() |
getDay() | The day of the week, a number from 0 (Sunday) to 6 (Saturday) | TODAY().getDay() |
Subtracting one date from another
When you subtract one date from another, the result is in milliseconds. Divide it to get the unit you need:
| To get | Divide by |
|---|---|
| Seconds | 1000 |
| Minutes | (60 * 1000) |
| Hours | (60 * 60 * 1000) |
| Days | (24 * 60 * 60 * 1000) |
Examples
How to calculate the age based on date of birth entered by the user?
TODAY().diff(date_of_birth, "years")
Add a Date question for the date of birth and a Short answer for the age, and write the formula in the age question. The form shows the age as soon as the date of birth is entered. Full article
How to calculate the days between two dates?
((Leave Till - Leave From) / (24 * 60 * 60 * 1000)) + 1
The difference between two dates is in milliseconds, so divide it by 24 * 60 * 60 * 1000 for days, and add 1 to include the start date. For example, 2 November to 6 November gives 5. Full article
How to add days, months or years to a date?
Inputdate.add(5,"days")
Inputdate.add(3,"months")
TODAY().add(1,"years")
Write the formula in a Date question. The new date shows as soon as the input date is entered. Full article
How to subtract days, months or years from a date?
Inputdate.subtract(7,"days")
Inputdate.subtract(2,"months")
TODAY().subtract(1,"years")
Write the formula in a Date question. The new date shows as soon as the input date is entered. Full article
How to calculate the end date based on the start date and term duration selected by the user?
start_date.add(number_of_months, "months")
Here the term is a Dropdown with the number choices 3, 6 and 12, and the formula goes in an End date question. If the choices are text, such as 3 months, 6 months and 1 year, turn them into numbers with IFS:
start_date.add(IFS(subscription == "3 months", 3, subscription == "6 months", 6, subscription == "1 year", 12, 1),"months")
How to calculate the number of days left in a month?
DATE(TODAY().year(), TODAY().month()+1, 0).date() - TODAY().date()
DATE(TODAY().year(), TODAY().month()+1, 0) is the last day of this month, so
its date() is the number of days in the month. Subtracting today's day of the
month leaves the days remaining. The form works it out again each day.
Full article
How to calculate the day of the week?
TODAY().getDay()
The result is a number from 0 to 6, Sunday to Saturday, so on a Monday it is 1. Change the question's Appearance to hidden and the day comes with every response without your users seeing it. Full article
How to calculate the start and end date of a work week?
TODAY().add(7-TODAY().getDay(),"days")
week_start_date.add(6, "days")
Write the first formula in a Week start date question and the second in a Week end date question. For a week from Sunday to Saturday, on Monday 2 November the next week starts on 8 November and ends on 14 November. Full article
How to automatically fill today's date in the form?
TODAY()
Write it in the Calculate option of a Date question. The form opens with today's date filled in, and people can't change it, because a question with a formula turns read only by itself. Full article
To accept only dates in a range, or a set number of days ahead, use the date question's validation rules. See Question settings.
Next steps
- Write other formulas: Calculated fields.
- Add up points: Score functions.
- Lock today's date: Question settings.