DAY()

Day of month from Date, DateTime, or Time

Returns the day of the month (1 to 31) that a specific date falls on from Date or DateTime. If a Time value is specified, returns a fixed default day. Returns 0 if day is not found.

Sample usage

DAY(TODAY()) returns today's day of the month. See also: TODAY()

DAY(TODAY()) + 1 returns tomorrow's day of the month.

DAY(TODAY()) - 1 returns yesterday's day of the month.

DAY([Birthday]) returns someone's birthday day of the month.

Ordinal day number

Convert today's day number to a numeric ordinal (1st, 2nd, 3rd, 4th, and so on):

CONCATENATE(
  DAY(TODAY()),
  IF(
    IN(
      DAY(TODAY()),
      LIST(11, 12, 13)
    ),
    "th",
    SWITCH(
      RIGHT(DAY(TODAY()), 1),
      "1", "st",
      "2", "nd",
      "3", "rd",
      "th"
    )
  )
)
  1. IF(..., ..., ...) determines whether today's day of the month is handled as an ordinal naming exception, or as normal.
  2. IN(..., ...) tests whether today's day of the month is one of the exceptions.
  3. DAY(TODAY()) returns today's day of the month (1-31).
  4. LIST(11, 12, 13) defines the list of numbers that are exception to the ordinal naming rules (e.g., 11 is 11th, not 11st; 12 is 12th, not 12nd).
  5. "th" is the suffix given to exception days.
  6. SWITCH(...) determines which suffix to give non-exceptions.
  7. RIGHT(DAY(TODAY()), 1) gets the rightmost digit of the day of the month.
  8. "1", "st", "2", "nd", and "3", "rd" gives days that end with 1 the suffix -st (e.g., 31st), 2 the suffix -nd (e.g., 2nd), and 3 -rd (e.g., 23rd).
  9. "th" is he suffix given all other (non-exception) days (e.g., 5th, 15th, 20th).
  10. CONCATENATE(DAY(TODAY()), ...) appends the computed suffix to today's day of the month number.

See also: CONCATENATE(), IF(), IN(), LIST(), RIGHT(), SWITCH(), TODAY()

Syntax

DAY(when)

  • when - A Date, DateTime, or Time value.

Note

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()

See also

Date and time expressions

DATE()

MONTH()

TODAY()

YEAR()

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

true
Search
Clear search
Close search
Google apps
Main menu