DAY()

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.

See also

DATE()

MONTH()

TODAY()

YEAR()