Date expressions

Updated 10 hours ago by 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.


How did we do?