Syntax

DAY(when)

Arguments

  • when (Date, DateTime, or Time)

Return Value

Number: the day of the month (1 to 31) specified by when if identifiable, a fixed default day if when is a Time value, or 0 if a day is otherwise not found.

Examples

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

DAY(TODAY()) + 1 : Tomorrow's day of the month.

DAY(TODAY()) - 1 : Yesterday's day of the month.

DAY([Birthday]) : Someone's birthday day of the month.

Ordinal Day Number

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

CONCATENATE(
  DAY(TODAY()),
  IF(
    IN(
      DAY(TODAY()),
      LIST(10, 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()

See Also

MONTH(), YEAR()

Did this answer your question?