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.

Functions

Current Date & Time

  • NOW() for the current DateTime on the user's device.
  • TODAY() for the current Date on the user's device. Equivalent to DATE(NOW()). See also DATE().
  • TIMENOW() for the current Time on the user's device. Equivalent to TIME(NOW()). See also 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.

Time Components

  • HOUR() for the hour component of a specific Duration.
  • MINUTE() for the minute component of a specific Duration.
  • SECOND() for the second component of a specific Duration.

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 Components

  • DAY() for the day of the month from a Date.
  • MONTH() for the month number from a Date.
  • YEAR() for the year from a Date.
  • WEEKDAY() for the day number from a Date. Sunday is 1, Saturday is 7.
  • WEEKNUM() for the week number from a Date. A week begins on Sunday.
  • WORKDAY() returns a date some number of days away, ignoring weekends and other designated days.

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 to NOW().
  • @(_TODAY) is equivalent to TODAY().
  • @(_TIMENOW) is equivalent to TIMENOW().

Examples

  • TODAY() + 1: adds one day to the current date (a Date value).
  • TODAY() - "12/30/2001": the Duration between today and December 30, 2001 (two Date values).
  • NOW() - 1: subtracts one day from the current time (a DateTime value).
  • IF(([StopWhen] > [StartWhen]), ([StopWhen] - [StartWhen]), (([StopWhen] + 24) - [StartWhen])): the Duration between StartWhen and StopWhen (two DateTime values). See also: IF().
  • TIMENOW() + "03:03:00": adds 3 hours 3 minutes to the current time (a Time value).
  • ([When] - TODAY()) = 7: a Yes/No value indicating whether the Date or DateTime value of the When column value is seven days from today's date (a Date value).
  • AND(([When] >= (TODAY() - 7)), ([When] <= TODAY())): a Yes/No value indicating whether the Date or DateTime value of the When column is within the past seven days. See also: AND().
  • @(_TODAY) > ([TargetWhen] + 1): a Yes/No value indicating whether the Date or DateTime value of the TargetWhen column is more than a day in the past.
  • IN(MINUTE([_THIS] - "00:00:00"), LIST(0, 15, 30, 45)): a Yes/No value indicating whether the minute component of the DateTime or Time value of the current column falls on the quarter hour (i.e., is 0, 15, 30, or 45). See also: IN(), LIST().
  • [Timestamp] > (NOW() - 1): a Yes/No value indicating whether the DateTime value of the Timestamp column is within 24 hours of the current date & time.
  • HOUR(TODAY() - [TargetDate]) / 24: number of days between today's date and the Date value given in the TargetDate column.
  • [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. See also: FLOOR().
  • 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): the date of the preceding Sunday.
  • TODAY() - (WEEKDAY(TODAY()) - 2): 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.

Did this answer your question?