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().
  • UTCNOW() for the current DateTime with no timezone offset (i.e., UTC 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.
  • EOMONTH() calculates the last day of a month some number of months away, factoring in different month lengths and leap years.
  • 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.

Formatting Dates and Times as text

  • TEXT() accepts a DateTime, Date, or Time 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 to NOW().
  • @(_TODAY) is equivalent to TODAY().
  • @(_TIMENOW) is equivalent to TIMENOW().

Examples that Compute Dates

  • TODAY() + 1: adds one day (a Number) to the current Date.
  • TODAY() - 3 : subtracts 3 days (a Number) from the current Date.
  • [StartDate] + 7 : adds 7 days (a Number) to the StartDate (a Date).
  • 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 (a Number) to the current Time.
  • TIMENOW() + "003:03:00" : adds 3 hours 3 minutes (a Duration) to the current Time.
  • TIMENOW() - "003:03:00" : subtracts 3 hours 3 minutes (a Duration) from the current Time.

Examples that Compute DateTimes

  • NOW() + 1 : adds one day (a Number) to the current DateTime.
  • NOW() - 3 : subtracts three days (a Number) from the current DateTime.
  • [TargetDateTime] + "012:59:00" : adds 12 hours 59 minutes (a Duration) to a DateTime value.
  • [TargetDate] + ([TargetTime] - "00:00:00")  : Creates a DateTime value from a Date value and a Time value.

Examples that Compute Durations

  • TIMENOW() - "12:30:00" : the Duration between the current Time and 12:30 PM (a Time).
  • NOW() - "03:15:30" : the Duration between the current Time and 3:15:30 AM (a Time) on December 30, 1899 (the default Date if none is included).
  • TODAY() - "12/30/2001" : the Duration between the current Date and December 30, 2001 (a Date).
  • [EndDate] - [StartDate] : the Duration between StartDate (a Date) at midnight and EndDate (a Date) at midnight.
  • IF(([StopWhen] > [StartWhen]), ([StopWhen] - [StartWhen]), (([StopWhen] + 24) - [StartWhen])) : the Duration between StartWhen and StopWhen (two DateTime values). See also: IF().

Examples that Compute Durations in Days, Months, or Years

  • HOUR(TODAY() - [TargetDate]) / 24 : number of days between today's date and the Date value given in the TargetDate column.
  • 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.
  • 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])) / (365.24 * 24.0)) : the number of years between the start and end date. See also: FLOOR().

Examples that Compare Dates, Times, and DateTimes

  • (TODAY() - [When]) = 7: a Yes/No value indicating whether the Date or DateTime value of the When column value is exactly seven days before today's Date.
  • ([When] - TODAY()) = 7 : a Yes/No value indicating whether the Date or DateTime value of the When column value is exactly seven days after today's Date.
  • AND([OrderDateTime] >= [StartDateTime], [OrderDateTime] <= [EndDateTime]) : a Yes/No value indicating whether the DateTime value of the OrderDateTime column is between the StartDateTime and EndDateTime field values. See also: AND().
  • AND([OrderDate] >= [StartDate], [OrderDate] <= [EndDate]) : a Yes/No value indicating whether the Date value of the OrderDate column is between the StartDate and EndDate field values. See also: AND().
  • 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.
  • [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.
  • [OrderDateTime] >= (NOW() - "001:30:00")  a Yes/No value indicating whether the DateTime value of the OrderDateTime  column is within 1 hour 30 minutes of the current DateTime.
  • 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().

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?