DAY

What it does

DAY(serial_number)

DAY projects a date serial onto its day-of-month component: floor the serial, map ⌊serial⌋ to a calendar day under the workbook's date system (see FUNC.DATE), and return that day's day-of-month as an integer, documented as 1 through 31.

Of the three decomposers this is the one where Excel's serial line stops agreeing with the calendar. Serial 60 denotes 29 February 1900, a date that did not exist; the day-of-month it reports is 29 in a February that had 28 days. Serial 0 denotes a "0 January 1900" that is not a day at all. So the documented 1–31 range is the range for real days; the phantoms sit outside the calendar the range describes.

Arguments

DAY(serial_number) — one required argument, the date whose day-of-month is wanted.

Microsoft's category-wide guidance applies: pass a serial from DATE, from another formula, or from a cell already holding a date, rather than text, whose interpretation depends on the locale's date order. Argument coercion is the ordinary scalar behaviour described in coercion and lifting.

Note that DAY takes a date, not a day count. DAY(45) is the day-of-month of 14 February 1900, not the number 45 reduced somehow — a confusion that shows up whenever someone reaches for DAY when they wanted DAYS.

Try it

Quirks

Result and edge cases

Returns a Number, documented as 1…31 for real calendar days.

• Fractional serials are floored; time of day is discarded.
• Serial 60 is the diagnostic input for this whole category. If the implementation carries the phantom leap day, DAY(60) is 29 and DAY(59)/DAY(61) are 28 and 1. If instead it delegates to a real calendar without the shift, the answers slide by one. Nothing else in the date category exposes the artefact this directly.
• Very large serials. The battery's maximum-double row returns a number rather than an error; see below.

Errors

As documented for this category:

• #NUM! when serial_number is out of range for the workbook's date-base value; negative serials are the unambiguous case.
• #VALUE! when the argument will not convert to a number — ordinary coercion, not a DAY rule.
• An error argument propagates.

How to implement

Notes for implementers

1. Implement the artefact as a coordinate shift, once. Convert serial to a proleptic Gregorian day number by subtracting 1 for serials ≥ 61, do real calendar arithmetic, and special-case serials 0 and 60 on the way out. Trying to teach a calendar library that 1900 was a leap year corrupts every other computation that library performs.
2. Share the serial→(y,m,d) routine with YEAR and MONTH; three separate derivations will eventually disagree at serial 60.
3. Decide what DAY(60) returns and write it down. It is the one value in this function that a reasonable engineer could get "right" in two different ways, and it is therefore the first thing a compatibility test should pin.

Rust code

5 day_fn "DAY" (1, 1) [V "serial_number"] |s, a, sv| {
    of_date(s, a, |x| k::serial_to_ymd(x).map(|(_, _, d)| d as f64))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• YEAR, MONTH — the sibling projections; together they invert DATE.
• DAYS — the confusable. DAY reads a component of one date; DAYS measures the interval between two. The names are one character apart and the meanings are unrelated.
• EOMONTH — DAY(EOMONTH(s,0)) is the idiomatic "how many days are in this month" formula, and is the standard way people discover the 1900 artefact by accident: DAY(EOMONTH(DATE(1900,2,1),0)) asks a question with two possible right answers.
• WEEKDAY — day of week rather than day of month, and the function Microsoft's leap-year article names as the one that returns incorrect values before 1 March 1900.

Named variants

No named variants are recorded yet.