Date expressions
Modified 4 years ago
Neil
Date and datetime expressions are very similar, but they cannot be mixed.
Timezones and format settings for date fields are set at the account or user level.
Example fields
We’ll use these sample fields in the expressions below.
FieldID | Field type | Value |
date1 | Date | 5/29/1970 |
date2 | Date | 6/3/1970 |
datetime1 | Datetime | 5/29/1970 3:30 PM |
datetime2 | Datetime | 5/30/1970 3:30 AM |
Functions
Functions go at the start of the expression.
Function | Description | Syntax | Output | Output type |
Date | Creates a date from numbers. | Date(1970, 5, 29) | 5/29/1970 | Date |
Datefromtext | Creates a date from text. | Datefromtext(1970/05/29) | 5/29/1970 | Date |
Datetime | Creates a date and time from numbers. Add a time zone. | Datetime(1970, 5, 29, 15, 30, 00, “America/Los Angeles”) | 5/29/1970 3:30 PM PST | Datetime |
Datediff | Shows the absolute value of the difference between two dates based on a unit. “Year” “Month” “Day” “Hour” “Minute” “Second” It will ignore non-working days by default. To count these, add false(). | Datediff(date1, date2, “Day”) Datediff(date1, date2, “Day”, false()) | 3 5 | Number |
Now | The date and time of the last time the form was modified, based on UTC. | Now() | [the present date and time] | Datetime |
Today | The present day, based on UTC. | Today() | [the present date] | Date |
Initiatedat | The date and time when the item was created as a draft, it is displayed in the user’s set time zone. | Initiatedat() | [the date and time the item was created as a draft] | Datetime |
Note: Now and Today will change whenever the form is modified or submitted. It works exactly like _modified_at.
Methods
Function | Description | Syntax | Output | Output type |
offset | Offsets the date and time. Y=year M=month D=day h=hour m=minutes=second | datetime1.offset(10,”D”) datetime1.offset(-10,”m”) | 06/08/1970 3:30 PM 05/29/1970 3:20 PM | Date or datetime |
eom | Shows the last day of the month for the given field. | datetime1.eom() | 05/31/1970 12:00 AM | Date or datetime |
week | Shows the week count for the given field. | datetime1.week() | 22 | Number |
weekday | Shows the weekday count for the given field. 1=Sunday 2=Monday, etc. You can change the default start day of the week. | datetime1.weekday() datetime1.weekday(2) | 6 5 | Number |
year | Shows the year of the given field. | datetime1.year | 1970 | Number |
month | Shows the month of the given field. | datetime1.month | 5 | Number |
day | Shows the day of the month of the given field. | datetime1.day | 29 | Number |
hour | Shows the hour of the given field. | datetime1.hour | 15 | Number |
minute | Shows the minute of the given field. | datetime1.minute | 30 | Number |
second | Shows the seconds of the given field. | datetime1.second | 0 | Number |
Operators
You can use the additon and subtraction operators with date and datetime fields.
Field 1 | Field 2 | Operator | Example | Result | Resulting type |
Date | Number/Integer | +, - | date1 + 5 | 6/3/1970 | Date |
Date | Date | - | date2 - date1 | 5 | Number |
Datetime1 | Number/Integer | +, - | datetime1 - 1 | 5/28/1970 3:30 PM | Datetime |
Datetime | Datetime | - | datetime2 - datetime1 | 0.5 | Number |
1. Integers represent 24 hours with a datetime field. If you want to subtract 12 hours, enter datetime1-0.5.
