Use advanced Formulas to make calculations using date and time functions.
What you'll need
- 60 uses of Custom Fields, including Formula Fields, are available on the Free Forever Plan.
- Unlimited uses are available on all paid plans.
- Individual Custom Fields can be hidden from guests.
- Guests with edit or full permissions can edit existing Custom Fields but cannot create new ones.
- Formula Fields do not support using Custom Fields that contain text.
Variables surrounded by {} in the following sections are optional variables and can be left out of a function without the function breaking. Ensure parentheses are acknowledged in your Formulas or you will receive an error message.
Simple date and time functions
Below is a table of our simplest date and time functions. All inputs are date objects and all outputs are numbers, except for TODAY() which has no inputs and simply outputs a date corresponding to the current day's date.
| Syntax | Example | Expected Result |
| DAY(date) | DAY('7/15/21') |
15 |
| DAYS(end_date, start_date) | DAYS('7/15/21', '6/15/21') |
30 |
| MONTH(date) | MONTH('7/15/21') |
7 |
| TODAY() | TODAY() |
Today |
| YEAR(date) | YEAR('7/15/21') |
2021 |
Complex date and time functions
Below is a table of more complex date and time functions.
| Syntax | Description | Example | Expected Result | ||||||||||||||||||||||
| DATE(year, month, day) | This function takes three numbers then returns a date object corresponding to the numbers that were input. | DATE(2021, 7, 15) |
7/15/21 | ||||||||||||||||||||||
| EDATE(start_date, months) |
This function takes a date object (start_date) and a number (months), then returns a date representing a specified number of months before or after the start_date. Negative numbers will allow you to access months before the start_date. Because this function returns a serial number rather than a date object, you will need to use the DATE() function to convert it to a date object. |
DATE(YEAR(EDATE('7/15/2021', -1)), MONTH(EDATE('7/15/2021', -1)), DAY(EDATE('7/15/2021', -1))) |
6/15/21 | ||||||||||||||||||||||
| EOMONTH(start_date, months) |
This functions takes a date object (start_date) and a number (months) then returns a date representing the last day of a month which falls a specified number of months before or after the start_date. Negative numbers will allow you to access months before the start_date. As this function returns a serial number rather than a date object, you will need to use the DATE() function to convert it to a date object. |
DATE(YEAR(EOMONTH('7/15/21', 0)), MONTH(EOMONTH('7/15/21', 0)), DAY(EOMONTH('7/15/21', 0))) |
7/31/21 | ||||||||||||||||||||||
| HOUR(time) | This functions takes a date object that includes time then returns a number corresponding to the hour of the time that was input. The number result will be based on a 24 hour clock. | HOUR('7/15/21 7:45:00 PM') |
19 | ||||||||||||||||||||||
| MINUTE(time) | This function takes a date object that includes time, then returns a number corresponding to the minute of the time that was input. | MINUTE('7/15/21 7:45:00 PM') |
45 | ||||||||||||||||||||||
| NETWORKDAYS(start_date, end_date, {holidays}) | This function takes two date objects and an array of date objects, then returns a number corresponding to the net working days between start_date and end_date, excluding weekends and any specified holidays. | NETWORKDAYS('7/15/21', '7/22/21', ['7/19/21', '7/20/21']) |
4 | ||||||||||||||||||||||
| WORKDAY(start_date, num_days, {holidays}) | This function takes a date object, a number, and an array of date objects, then returns a date object corresponding to the number of working days after start_date, excluding weekends and any specified holidays. | WORKDAY('7/15/21', 7, ['7/19/21', '7/20/21']) |
7/28/21 | ||||||||||||||||||||||
| WEEKDAY(date, {type}) |
This function takes a date object and a number then returns a number representing the day of the week that the specified date lands on. The type variable is a bit more complex. Here's a table displaying what each number means for this function:
|
WEEKDAY('7/15/21', 2) |
4 | ||||||||||||||||||||||
| WEEKNUM(date, {type}) |
This function takes a date object and a number then returns a number representing the week of the year that the specified date lands on. Type 21 follows the methodology specified by ISO 8601, which is commonly known as the European week numbering system. The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.
|
|
29 |