Date and time expressions use Date
or Duration
values to produce a DateTime
, Date
, Time
, Duration
, or Number
value.
The following sections describe the components for building date and time expressions:
- Current date and time functions
- Time component functions
- Date component functions
- Legacy operators
- Examples
New to expressions and formulas? See also Expressions: The Essentials.
Current date and time functions
NOW()
for the currentDateTime
on the user's device.TIMENOW()
for the currentTime
on the user's device. Equivalent toTIME(NOW())
. See alsoTIME()
.TODAY()
for the currentDate
on the user's device. Equivalent toDATE(NOW())
. See alsoDATE()
.UTCNOW()
for the currentDateTime
in Coordinated Universal Time (UTC).
The values returned by NOW()
, TODAY()
, and TIMENOW()
reflect the timezone offset of the user's device. For example, if the timezone of the user's device is Pacific Standard Time (PST), the value returned is UTC-08:00; if the timezone of the user's device is Hong Kong, the value returned is UTC+8:00.
When the user's device contacts the server to read or update data, the device includes its timezone with the request. The server uses the timezone of the user's device when performing time and date calculations. For example, when the server evaluates security filters and workflow rules that include dates and times.
To understand how your mobile device's locale or browser's language setting can impact the format of the returned value, see Considerations for apps using Date, Time, and DateTime formats.
Time component functions
EXTRACTDURATIONS()
to extract a list ofDuration
values within a textual value.EXTRACTTIMES()
to extract a list ofTime
values within a textual value.HOUR()
for the hour component of a specificDuration
.MINUTE()
for the minute component of a specificDuration
.SECOND()
for the second component of a specificDuration
.TIME()
for theTime
fromDate
,DateTime
, orTime
.TOTALHOURS()
for count of hours inDuration
.TOTALMINUTES()
for count of minutes inDuration
.TOTALSECONDS()
for count of seconds inDuration
.
Note that each of HOUR()
, MINUTE()
, and SECOND()
accept as input a Duration
value, not a Time
value. To convert a Time
value to a Duration
value, subtract another Time
value. For instance, to convert the current time-of-day to a Duration
: TIMENOW() - "00:00:00"
.
Date component functions
DATE()
for theDate
fromDate
,DateTime
, orTime
.DATETIME()
for theDateTime
fromDate
,DateTime
, orTime
.DAY()
for the day of the month from aDate
.EOMONTH()
calculates the last day of a month some number of months away, factoring in different month lengths and leap years.EOWEEK()
calculates the date of the last day of a week fromDate
orDateTime
.EWOMONTH()
calculates the date of the last weekday (Monday through Friday) of the month specified by theDate
orDateTime
value.EXTRACTDATES()
to extract a list ofDate
values within a textual value.EXTRACTDATETIMES()
to extract a list ofDateTime
values within a textual value.ISOWEEKNUM()
for the ISO week number fromDate
orDateTime
.MONTH()
for the month number from aDate
.WEEKDAY()
for the day number from aDate
. Sunday is1
, Saturday is7
.WEEKNUM()
for the week number from aDate
. A week begins on Sunday.WORKDAY()
returns a date some number of days away, ignoring weekends and other designated days.YEAR()
for the year from aDate
.
Some constant values, such as "MM/DD/YYYY"
, are evaluated as a Date
value by AppSheet. Similarly, "000:00:00"
is evaluated as a Duration
value. This doesn't mean your spreadsheet data must use the same formats: the date and time formats in your spreadsheets are determined by the locale/language setting. Column will always be evaluated as the type of column. Additionally, you can convert data, both columns and string literals, to specific types using functions such as DATE()
, TIME()
, or DATETIME()
.
Formatting dates and times as text
TEXT()
accepts aDateTime
,Date
, orTime
and a format string, and returns a text representation.
Legacy operators
For backwards compatibility, we also support the function syntax below for a set of functions that have been supported from the earliest AppSheet release.
@(_NOW)
is equivalent toNOW()
.@(_TODAY)
is equivalent toTODAY()
.@(_TIMENOW)
is equivalent toTIMENOW()
.
Examples
Examples that compute Dates
TODAY() + 1
: adds one day (aNumber
) to the currentDate
.TODAY() - 3
: subtracts 3 days (aNumber
) from the currentDate
.[StartDate] + 7
: adds 7 days (aNumber
) to theStartDate
(aDate
).TODAY() - (WEEKDAY(TODAY()) - 1)
: the date of the preceding Sunday.TODAY() - (WEEKDAY(TODAY()) - 2)
: the date of the preceding Monday.
Examples that compute Times
TIMENOW() + 1
: adds one hour (aNumber
) to the currentTime
.TIMENOW() + "003:03:00"
: adds 3 hours 3 minutes (aDuration
) to the currentTime
.TIMENOW() - "003:03:00"
: subtracts 3 hours 3 minutes (aDuration
) from the currentTime
.
Examples that compute DateTimes
NOW() + 1
: adds one day (aNumber
) to the currentDateTime
.NOW() - 3
: subtracts three days (aNumber
) from the currentDateTime
.[TargetDateTime] + "012:59:00"
: adds 12 hours 59 minutes (aDuration
) to aDateTime
value.[TargetDate] + ([TargetTime] - "00:00:00")
: Creates aDateTime
value from aDate
value and aTime
value.
Examples that compute Durations
TIMENOW() - "12:30:00"
: theDuration
between the currentTime
and 12:30 PM (aTime
).NOW() - "03:15:30"
: theDuration
between the currentTime
and 3:15:30 AM (aTime
) on December 30, 1899 (the defaultDate
if none is included).TODAY() - "12/30/2001"
: theDuration
between the currentDate
and December 30, 2001 (aDate
).[EndDate] - [StartDate]
: theDuration
betweenStartDate
(aDate
) at midnight andEndDate
(aDate
) at midnight.IF(([StopWhen] > [StartWhen]), ([StopWhen] - [StartWhen]), (([StopWhen] + 24) - [StartWhen]))
: theDuration
betweenStartWhen
andStopWhen
(twoDateTime
values). See alsoIF()
.
Examples that compute Durations in Days, Months, or Years
HOUR(TODAY() - [TargetDate]) / 24
: number of days between today's date and theDate
value given in theTargetDate
column.((YEAR([EndDate]) - YEAR([StartDate])))
: the number of years between the start and end dates.((((YEAR([EndDate]) - YEAR([StartDate])) * 12) + MONTH([EndDate])) - MONTH([StartDate]))
: the number of months between the start and end dates.
Examples that compare Dates, Times, and DateTimes
(TODAY() - [When]) = 7
: aYes/No
value indicating whether theDate
orDateTime
value of theWhen
column value is exactly seven days before today'sDate
.(EOMONTH([When], 0) = EOMONTH(TODAY(), 0))
: aYes/No
value indicating whether theDate
orDateTime
value of theWhen
column value is in the same month as today's date. See also:EOMONTH()
,TODAY()
([When] - TODAY()) = 7
: aYes/No
value indicating whether theDate
orDateTime
value of theWhen
column value is exactly seven days after today'sDate
.-
AND([OrderDateTime] >= [StartDateTime], [OrderDateTime] <= [EndDateTime])
: aYes/No
value indicating whether theDateTime
value of theOrderDateTime
column is between theStartDateTime
andEndDateTime
column values. See also:AND()
AND([OrderDate] >= [StartDate], [OrderDate] <= [EndDate])
: aYes/No
value indicating whether theDate
value of theOrderDate
column is between theStartDate
andEndDate
column values. See also:AND()
AND(([When] >= (TODAY() - 7)), ([When] <= TODAY()))
: aYes/No
value indicating whether theDate
orDateTime
value of theWhen
column is within the past seven days. See also:AND()
TODAY() > ([TargetWhen] + 1)
: aYes/No
value indicating whether theDate
orDateTime
value of theTargetWhen
column is more than a day in the past.[Timestamp] > (NOW() - 1)
: aYes/No
value indicating whether theDateTime
value of theTimestamp
column is within 24 hours of the current date and time.[OrderDateTime] >= (NOW() - "001:30:00")
aYes/No
value indicating whether theDateTime
value of theOrderDateTime
column is within 1 hour 30 minutes of the currentDateTime
.-
IN(MINUTE([_THIS] - "00:00:00"), LIST(0, 15, 30, 45))
: aYes/No
value indicating whether the minute component of theDateTime
orTime
value of the current column falls on the quarter hour (that is, is 0, 15, 30, or 45). See also:IN()
,LIST()
Examples for scheduling
-
AND((TODAY() >= DATE("01/01/2021")),
: a
(TODAY() <= DATE("12/31/2021")),
(MOD(HOUR(TODAY()- DATE("01/01/2021"))/24, 15) = 0))Yes/No
value for a scheduled periodic event. TheDATE
value in the first line specifies the starting date of the scheduled periodic event. TheDATE
value in the second line specifies the ending date of the scheduled periodic event. TheDATE
value in the third line specifies when the scheduled periodic event should first be triggered. Normally theDATE
values in the first and third lines should be identical, but you could specify a slightly laterDATE
value in the third line to trigger the first scheduled periodic event at a slightly later date. The value 15 in the third line specifies that the scheduled periodic event should be triggered every 15 days after theDate
specified in the third line.