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.

FunctionSyntaxReturnsExample
DATEDATE(year, month, day)A date from a year, month and dayDATE(2020, 9, 25)
TODAYTODAY()The current dateTODAY()
NOWNOW()The current date and timeNOW()
ADDinputdate.add(number, term)The date with seconds, minutes, hours, days, months or years addedTODAY().add(3,"days")
SUBTRACTinputdate.subtract(number, term)The date with seconds, minutes, hours, days, months or years subtractedTODAY().subtract(6,"months")
DIFFenddate.diff(startdate, unit)The number of seconds, minutes, hours, days, months or years between two datesTODAY().diff(DATE(1950,12,12),"years")

Full article

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 as enddate.
  • 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

FunctionReturnsExample
year()The yearTODAY().year()
month()The monthTODAY().month()
date() or getDate()The day of the monthTODAY().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 getDivide by
Seconds1000
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")

Full article

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