Excel DAY, MONTH, and YEAR Functions
last modified October 5, 2026
The DAY, MONTH, and YEAR functions are
essential date functions in Excel. They extract a single component - the day,
month, or year - from a date value. This tutorial provides a comprehensive guide
to using these functions. You'll learn their syntax, how Excel stores dates as
serial numbers, how to avoid text-date pitfalls, and how to combine the three
functions with DATE for real date calculations.
DAY, MONTH, and YEAR Function Basics
All three functions take one argument, a date, and return a plain number. They
do not return a formatted date, so no date number format is needed to read the
result. DAY returns the day of the month, MONTH the
month number, and YEAR the four-digit year.
| Component | Description |
|---|---|
| Function Name | DAY, MONTH, YEAR |
| Syntax | =DAY(serial_number), =MONTH(serial_number), =YEAR(serial_number) |
| Arguments | serial_number - the date to extract from (one argument, required) |
| Return Value | A plain number: day 1-31, month 1-12, or year 1900-9999 |
This table summarizes the three functions. Each returns an integer, never a date, and each accepts exactly one argument. Supplying more than one argument produces a "too many arguments" error.
| Argument | Meaning |
|---|---|
| serial_number | The date to read. It can be a serial number such as 44941,
a date text string such as "2023-01-15", a cell reference, or
another formula that returns a date. |
The name serial_number is the key to understanding these functions:
Excel stores every date as a whole number, and DAY,
MONTH, and YEAR simply decode that number.
Excel Date Serial Numbers
Excel does not store a date as text. It stores a whole number, called a serial
number, that counts the days from a fixed starting point. In the default 1900
date system, January 1, 1900 is serial number 1 and every following day adds
one. A cell that displays 1/15/2023 really contains the number 44941 with a date
number format applied. This is why DAY, MONTH, and
YEAR return plain numbers rather than dates.
| Date | Serial number |
|---|---|
| January 0, 1900 (fictitious) | 0 |
| January 1, 1900 | 1 |
| January 15, 2023 | 44941 |
| January 1, 2024 | 45292 |
| October 5, 2026 | 46300 |
Serial number 0 represents January 0, 1900, a fictitious day before January 1,
1900. It is a useful edge case: =DAY(0) returns 0,
=MONTH(0) returns 1, and =YEAR(0) returns 1900.
Negative serial numbers are not valid dates and return #NUM!.
=DATE(2023, 1, 15) ' returns 44941 =DATE(2024, 1, 1) ' returns 45292 =DATE(2026, 10, 5) ' returns 46300
Basic DAY Example
This example demonstrates extracting the day number from a date text string.
=DAY("2023-01-15")
This formula returns 15, extracting the day from January 15, 2023. The date can be entered as text (in quotes), as a serial number, or as a cell reference that holds a date. The result 15 is a plain number with no date formatting.
Basic MONTH Example
This example shows how to extract the month number from a date value in a cell.
| A | B |
|---|---|
| 2025-07-22 | =MONTH(A1) |
The table shows a date in cell A1 and the MONTH formula in B1 that extracts the month number. The result will be 7 for July.
=MONTH(A1)
This formula returns the month number from the date in cell A1. When A1
contains July 22, 2025, it returns 7. The same cell could also be read with
=MONTH(45860), because 45860 is the serial number for that date.
Basic YEAR Example
This example extracts the year from a date stored in a cell.
| A | B |
|---|---|
| 1999-12-31 | =YEAR(A1) |
The table contains New Year's Eve 1999 in cell A1. The YEAR formula in B1 extracts just the year portion, returning 1999.
=YEAR(A1)
This formula returns the year from the date in A1. For December 31, 1999, it returns 1999. YEAR is useful for grouping or analyzing data by year, because the result is a sortable number rather than text.
Extracting All Three Parts
The three functions are often combined to break a date into its components or to build a custom text label. This example shows a common combination.
| A | B |
|---|---|
| 2023-03-08 | =DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) |
The table demonstrates combining all three functions to create a day/month/year format. The ampersand (&) concatenates the components with slashes.
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1)
This formula creates a date string in d/m/y format from the date in A1. For
March 8, 2023, it returns "8/3/2023". Note that the result is text, not a date,
so it cannot be used directly in date arithmetic. To build a real date from the
parts, use DATE as shown later.
Text Dates and Locale Pitfalls
When the argument is text, Excel has to guess the order of the year, month, and
day. It uses the system locale, so "01/02/2023" means January 2 in
the United States but February 1 in much of Europe. This ambiguity is a common
source of wrong results.
The safest text format is the unambiguous ISO form "2023-01-15"
(year first), which Excel reads the same way in every locale. Better still,
store dates as real date values with DATE or DATEVALUE
instead of leaving them as text. If the text cannot be recognized as a date, the
functions return a #VALUE! error.
| Input | Result |
|---|---|
| "2023-01-15" (ISO text) | DAY returns 15, MONTH returns 1, YEAR returns 2023 |
| "15-Jan-2023" (recognized text) | DAY returns 15, MONTH returns 1, YEAR returns 2023 |
| "not a date" (invalid text) | #VALUE! error |
| -1 (negative serial) | #NUM! error |
=DAY("2023-01-15") ' 15, unambiguous ISO text
=DAY("not a date") ' #VALUE!, text is not a date
=DAY(-1) ' #NUM!, negative serial
Combining with DATE
A frequent task is to shift a date by a number of months while keeping the same
day. Extract the parts with YEAR, MONTH, and
DAY, change the part you need, then feed everything back into
DATE. DATE rolls overflowing months into the next year
automatically.
| A | B |
|---|---|
| 2025-08-15 | =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) |
The table shows adding 3 months to a date by extracting the components, modifying the month, and rebuilding the date.
=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
This formula adds 3 months to the date in A1. DATE automatically adjusts the
year when the month exceeds 12, so August 15, 2025 plus 3 months returns
November 15, 2025. The same pattern works for years: add to the
YEAR part to shift by whole years.
Calculating Age
A practical application is calculating age from a birthdate. This example uses YEAR and TODAY functions.
| A | B |
|---|---|
| 1990-05-15 | =YEAR(TODAY())-YEAR(A1) |
The table shows a birthdate in A1 and an age calculation in B1. This simple formula subtracts the birth year from the current year to estimate age.
=YEAR(TODAY())-YEAR(A1)
This formula calculates age by subtracting the birth year from the current year.
It does not account for whether the birthday has occurred yet this year, so it
can overstate the age by one. For more precision, combine with
MONTH and DAY.
Precise Age Calculation
This improved age calculation accounts for the month and day of birth.
=YEAR(TODAY())-YEAR(A1)-IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),1,0)
This formula first checks whether this year's birthday is still in the future. If it is, it subtracts 1 from the simple year difference. The result is the exact age in whole years, accounting for the birth month and day.
Quarter from a Date
Combine MONTH with simple math to calculate calendar quarters.
| A | B |
|---|---|
| 2025-11-20 | =CEILING(MONTH(A1)/3,1) |
The table demonstrates calculating a quarter from a date. The formula divides the month by 3 and rounds up to the nearest integer to determine the quarter (1-4).
=CEILING(MONTH(A1)/3,1)
This formula returns the quarter (1-4) for the date in A1. For November (month
11), it returns 4. CEILING rounds the division result up to the
nearest integer, so months 1-3 give 1, months 4-6 give 2, and so on.
Month Name from a Date
Combine MONTH with TEXT to get month names instead of
numbers.
| A | B |
|---|---|
| 2025-12-25 | =TEXT(DATE(1,MONTH(A1),1),"mmmm") |
The table demonstrates converting a month number to a full month name. The formula creates a dummy date using the month from A1 and formats it to show the name.
=TEXT(DATE(1,MONTH(A1),1),"mmmm")
This formula returns the full month name "December" for the date in A1. The
DATE(1,...) part is a trick: because years 0-1899 are offset by
1900, year 1 becomes 1901, but only the month matters for the "mmmm" format.
Because A1 is already a real date, the shorter =TEXT(A1,"mmmm")
gives the same result.
Days Until Next Birthday
The same components can measure how many days remain until the next birthday.
=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()
This formula builds this year's birthday from the month and day in A1. If that
date has already passed, the comparison <TODAY() is TRUE, which
adds 1 to the year and moves the birthday to next year. Subtracting
TODAY() then gives the number of days remaining.
Serial Number Edge Cases
A few boundary values are worth remembering because they explain otherwise surprising results.
| Input | DAY | MONTH | YEAR |
|---|---|---|---|
| 0 | 0 | 1 | 1900 |
| 1 | 1 | 1 | 1900 |
| 44941 | 15 | 1 | 2023 |
| 46300 | 5 | 10 | 2026 |
| -1 | #NUM! | #NUM! | #NUM! |
Serial 0 is the fictitious January 0, 1900, so DAY(0) is 0 rather
than 1. Serial 1 is January 1, 1900. Any negative serial number is invalid and
returns #NUM!. When a DATE result rolls over, for
example =DATE(2023,15,1) which becomes March 1, 2024, the component
functions report the rolled-over date: YEAR returns 2024,
MONTH returns 3, and DAY returns 1.
=YEAR(DATE(2023,15,1)) ' 2024 =MONTH(DATE(2023,15,1)) ' 3 =DAY(DATE(2023,15,1)) ' 1
DAY, MONTH, and YEAR Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Date entered as unrecognized text | #VALUE! error | Use a real date, or an ISO string such as "2023-01-15" |
| Negative serial number | #NUM! error | Dates before January 1, 1900 are not supported |
| Ambiguous text date such as "01/02/2023" | Day and month swapped | Use the ISO year-first format or store a real date |
| More than one argument | Too many arguments error | Each function accepts a single argument |
| Cell shows a number such as 44941 | Serial number instead of a date | Apply a date number format with Ctrl+1 |
| Empty cell passed as the argument | DAY returns 0, MONTH returns 1, YEAR returns 1900 | An empty cell is treated as serial 0 |
The DAY, MONTH, and YEAR functions are
the building blocks of date work in Excel. They decode the serial number behind
every date and return plain numbers that are easy to sort, compare, and feed
back into DATE. Once you know the 1900 serial system, the
day/month/year order pitfalls of text dates, and the behavior at serial 0, you
can extract date parts, calculate ages and quarters, and rebuild dates without
surprises. Master these three functions and the rest of Excel's date toolset
becomes far easier to use.
Author
List all Excel Formulas.