New to expressions and formulas? Try Expressions: The Essentials.

You can also learn more about other types of expressions.

Date and time expressions use Date or Duration values to produce a DateTime, Date, Time, Duration, or Number value.

AppSheet conditions aren't a one-to-one match with Google Sheets functions; however, in some cases the formatting similarity may help you construct expressions. Alternatively, if the formatting isn't 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.

Functions

  • NOW() for the current DateTime on the user's device (NOW)
  • TODAY() for the current Date on the user's device (TODAY)
  • TIMENOW() for the current Time on the user's device (TIME). 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.
  • 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)
  • WORKDAY(date, number) returns a date some number of days away, ignoring weekends (WORKDAY)

The value returned by WEEKNUM() assumes the week starts on Sunday and ends on Saturday. To get the week number when the week starts on Monday and ends on Sunday, 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 date-time.
  • TODAY() - "12/30/2001": returns the Duration between the two Date values.
  • IF(([StopTime] > [StartTime]), ([StopTime] - [StartTime]), (([StopTime] + 24) - [StartTime])): returns the Duration between two Time values.
  • TIMENOW() + "03:03:00": adds 3 hours 3 minutes to the current time.
  • AND([Date]>=TODAY()-7,[Date]<=TODAY()); return true if Date is within the past seven days.

Complex Expressions

  • @(_TODAY) > ([TargetDate] + 1): checks if [TargetDate] is more than a day old.
  • AND(TRUE, IN(MINUTE([_THIS] - "00:00:00"), LIST(0, 15, 30, 45))): ensures that the minute value falls on the quarter hour, that is 00, 15, 30, or 45.
  • [ChangeTimestamp] > (NOW() - 1): checks if a DateTime is within 1 day of the current date-time. In this case the expression is checking the value of a ChangeTimestamp column, but this expression works for normal DateTime columns values also.
  • [LastUpdatedDateTime] > NOW()-"003:30:00": checks if a DateTime is within 3 hours and 30 minutes of the current date-time. In this case the expression is checking the value of a DateTime column, but this expression works for ChangeTimestamp columns also.
  • HOUR(TODAY() - [TargetDate]) / 24: the number of days since [TargetDate].
  • [TargetDate] + ([TargetTime] - "00:00:00"): adds [TargetTime] and [TargetDate].
  • FLOOR((HOUR([EndDate] - [StartDate])) / (365.24 * 24.0)): the number of years between the start and end date.
  • FLOOR(((HOUR([EndDate] - [StartDate])) - ([Years] * 365.25 * 24.0)) / (30.4 * 24.0)): the number of months between the start and end dates. It assumes virtual column Years contains the number of years computed using the formula above.
  • FLOOR(((HOUR([EndDate] - [StartDate])) - (([Years] * 365.24 * 24.0) + ([Months] * 30.4 * 24.0))) / 24.0): the number of days between the start and end dates. It assumes virtual columns Years and Months contain the number of years and months, respectively, computed using the formulas above.
  • TODAY() - (WEEKDAY(TODAY()) - 1): Returns the date of the preceding Sunday.
  • TODAY() - (WEEKDAY(TODAY()) - 2): Returns the date of the preceding Monday.

Note that when dates are used as constant values within an expression, they must be represented in the MM/DD/YYYY format. This doesn't mean 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

In 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] - (NOW() + 1)

{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"

Did this answer your question?