Date and time expressions

Expressions may be used in various AppSheet features -- Initial Values, App Formulas, Virtual Columns and Column Constraints (Valid_If, Show_If, Required_If) -- to customize app behavior and provide your users with advanced functionality. Expressions in this article align with the Time Expressions in the Expression Builder of the Editor. The Expression Builder can be found anywhere you are able to enter a formula/expression, noted by a little "flask" symbol next to it. Clicking on the flask will bring up the Expression Builder. The Expression Builder is "context-aware," i.e. it shows you expressions that are relevant to the specific table you are editing. Also included in the builder is an "instant" expression checker, to verify that the expression is valid.

 

Expression components

Use any of the following values as part of an expression:

Constants

  • Words, Dates, Times. Highlight all values with "quotes", except for numeric values, e.g. "Value""01/17/2016""12:00:00" for time, "012:00:00" for duration.
  • Numeric values are noted just as they are, e.g 10.

Column Names

  • Name any column using square brackets around the exact column name: [ColumnName]. When combining a [ColumnName] with another value, put the expression in (parentheses). May be used in any expression; however, when used in the Initial Value feature, it may only refer to a separate table.

 

Temporal conditions

Temporal Expressions utilize Dates or Durations to return a DateTime, Date, Time, Duration or Number value. AppSheet conditions are not a 1:1 match with Google Sheets functions; however, in some cases the formatting similarity may help you construct your expressions. Alternatively, if the formatting is not similar, the Google Sheets function page may provide context for use of the function. If available, see the Google Sheets function link in parentheses next to applicable operators. 

Current operators

  • NOW() for the current DateTime on the client (NOW)
  • TODAY() for the current Date on the client (TODAY)
  • TIMENOW() for the current Time on the client (TIME)

    The values returned by NOW(), TODAY(), and TIMENOW() reflect the timezone offset of the client. For example, if the client device timezone is Pacific Standard Time, the value returned is UTC - 08:00. If the client device timezone is Hong Kong, the value returned is UTC + 8:00. When the client calls the server to read or update date, the client includes the device's timezone with the request. The server uses the device's timezone when performing time and date calculations. For example, this includes the calculations performed when the server evaluates Security Filters and workflow rules that include dates and times.
  • HOUR({Duration}) for the Hour component of a specific Duration  (HOUR)
  • MINUTE({Duration}) for the Minute component of a specific Duration  (MINUTE)
  • SECOND({Duration}) for the Second component of a specific Duration  (SECOND)
  • DAY({Date}) for the Day of the Month  (DAY)
  • MONTH({Date}) for the Month Number from a Date  (MONTH)
  • YEAR({Date}) for the Year from a Date  (YEAR)
  • WEEKDAY({Date}) for the Day Number from a Date  (WEEKDAY)
  • WEEKNUM({Date}) for the Week Number from a Date  (WEEKNUM)

The value returned by WEEKNUM() assumes the week starts on Sunday and ends on Saturday. If you wish to get the week number when the week starts on Monday and ends on Sunday you can use the following expression

IF(AND(MONTH([Date])=1,Day([Date])=1), 1, WEEKNUM([Date]-1))

 

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) for the current DateTime  (NOW)
  • @(_TODAY) for the current Date  (TODAY)
  • @(_TIMENOW) for the current Time  (TIME)

 

Common and complex expressions

Common expressions

  • TODAY() + 1: adds 1 day to the current Date 
  • NOW() - 1: subtracts 1 day from the current DateTime
  • TODAY() - "12/30/2001": returns a Duration between the two dates
  • TIMENOW() + "03:03:00": adds 3 hours and 3 minutes to the current Time

Complex expressions

  • @(_TODAY)>([TargetDate]+1) : checks if TargetDate is more than a day old
  • HOUR(TODAY() - [TargetDate]) / 24 : the number of days since TargetDate

Note that when dates are used as constant values within an expression, they must be represented in US date format "mm/dd/yyyy". This does not mean that your spreadsheet data must use dates in this format. The date representation in your spreadsheets is determined by the locale/language of the spreadsheet.

 

Time expression patterns, results and examples

From the Expression Builder, follow the pattern below for a DateTime, Date, Time, Duration or Number result. See the results and examples for further clarity.

Pattern Result Example
NOW() DateTime NOW()
TODAY() Date TODAY()
TIMENOW() Time TIMENOW()
HOUR({Duration}) Number HOUR([Duration])
MINUTE({Duration}) Number MINUTE([Duration])
SECOND({Duration}) Number SECOND([Duration])
DAY({Date}) Number DAY([Date])
MONTH({Date}) Number MONTH([Date])
YEAR({Date}) Number YEAR([Date])
WEEKDAY({Date}) Number WEEKDAY([Date])
WEEKNUM({Date}) Number WEEKNUM([Date])
{value_1} + {number} Date [Date] + 1
{value_1} - {value_2} Duration [Date] - (TODAY() + 1)
{value_1} - {duration} Date [Date] - "002:00:00"
{value_1} - {number} DateTime [DateTime] - 1
{value_1} - {value_2} Duration [DateTime] - (NOW() + 1)
{value_1} + {duration} DateTime [DateTime] + "002:00:00"
{value_1} + {number} Duration [Duration] + 1
{value_1} - {value_2} Duration [Duration] - (0:00:00 + 1)
{value_1} - {duration} Duration [Duration] - "002:00:00"
{value_1} - {number} Time [Time] - 1
{value_1} - {value_2} Duration [Time] - (TIMENOW() + 1)
{value_1} + {duration} Time [Time] + "002:00:00"
Have more questions? Submit a request

0 Comments

Article is closed for comments.