Excel DATE Function
last modified October 5, 2026
The DATE function is a fundamental date function in Excel that
creates valid dates from individual year, month, and day components. This
tutorial provides a comprehensive guide to using the DATE function.
You'll learn basic syntax, practical applications, and advanced techniques to
master this essential Excel function for date manipulation and calculations.
DATE Function Basics
The DATE function constructs a date value from year, month, and
day components. It handles date arithmetic automatically and corrects invalid
dates. The syntax is straightforward but powerful for date manipulation.
| Component | Description |
|---|---|
| Function Name | DATE |
| Syntax | =DATE(year, month, day) |
| Arguments | year, month, day (all required) |
| Return Value | Excel date serial number |
This table breaks down the essential components of the DATE
function. It shows the function name, basic syntax format, required arguments,
and return value characteristics. The function returns an Excel serial date.
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 5/15/2023 really contains the number 45061 with a
date number format applied. This is why DATE returns a number and
not a formatted date: you must apply a date format to see a calendar date.
| Date | Serial number |
|---|---|
| January 1, 1900 | 1 |
| February 28, 1900 | 59 |
| March 1, 1900 | 61 |
| January 1, 2023 | 44927 |
| January 1, 2024 | 45292 |
| January 1, 2025 | 45658 |
| October 5, 2026 | 46300 |
Notice the gap between serial 59 (February 28, 1900) and serial 61 (March 1, 1900). Serial number 60 is reserved for February 29, 1900, a date that never existed. Excel inherited this leap-year bug from Lotus 1-2-3 and keeps it for compatibility. The error only affects dates before March 1, 1900; every later serial number matches a strict day count.
=DATE(2023, 1, 1) ' returns 44927 =TEXT(DATE(2023, 1, 1), "dd/mm/yyyy") ' returns "01/01/2023"
DATE Year Handling
The year argument does not always mean the literal year you type.
Excel applies three rules, and they are a common source of wrong dates.
| Year argument | Excel behavior | Example |
|---|---|---|
| 0 to 1899 | Excel adds 1900 | =DATE(100,1,1) returns January 1, 2000 |
| 1900 to 9999 | Used exactly as written | =DATE(2024,1,1) returns January 1, 2024 |
| Less than 0 or 10000 or greater | #NUM! error | =DATE(-1,1,1) returns #NUM! |
The 0-1899 rule exists because Excel accepts two-digit years. Typing 24 is
interpreted as 1924, not 2024, because 1900 + 24 = 1924. Always supply
four-digit years when you want modern dates. Excel also returns
#NUM! when the assembled result falls outside the supported range,
for example =DATE(9999,12,32), which would roll past December 31,
9999.
The three arguments are numeric. Non-integer values are truncated to their
integer part, so =DATE(2023.9, 5.9, 15.9) produces the same date
as =DATE(2023, 5, 15). Text arguments are not accepted and return
a #VALUE! error.
Basic DATE Example
This example demonstrates the simplest use of the DATE function with hard-coded year, month, and day values.
=DATE(2023, 5, 15)
This formula creates a date for May 15, 2023. Excel will display this as 5/15/2023 (format depending on regional settings). The function returns the Excel date serial number 45061.
DATE with Cell References
A more practical use of DATE involves referencing cells for year,
month, and day components. Here's an example with cell references.
| A | B | C | D |
|---|---|---|---|
| 2023 | 12 | 25 | =DATE(A1, B1, C1) |
The table shows a simple spreadsheet with year, month, and day values in cells A1, B1, and C1. The DATE formula in D1 creates a date from these components.
=DATE(A1, B1, C1)
This formula creates December 25, 2023 from the values in cells A1 (2023), B1 (12), and C1 (25). The result appears as a formatted date (12/25/2023).
Building Dates from Parts
A frequent task is to reassemble a date from pieces that live in different
places. When the year, month, and day are already in separate cells, feed them
straight into DATE. When they are packed into one text string such
as 20231225, extract them first with LEFT,
MID, and RIGHT.
| A | B |
|---|---|
| 20231225 | =DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2)) |
The table stores a date in the compact text form 20231225 in A1.
The formula in B1 splits the string into its year, month, and day parts.
=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2))
The LEFT function returns "2023", MID returns "12",
and RIGHT returns "25". DATE converts the three text
values to numbers and returns December 25, 2023. The same idea applies to any
fixed-width layout: split the string into year, month, and day, then let
DATE validate and assemble them.
DATE with Automatic Month Correction
DATE automatically handles month values greater than 12 by incrementing the year. This example shows this behavior.
| A | B | C | D |
|---|---|---|---|
| 2023 | 15 | 1 | =DATE(A1, B1, C1) |
This table demonstrates DATE's ability to correct invalid month values. The month value 15 is automatically converted to March of the following year.
=DATE(2023, 15, 1)
This formula creates March 1, 2024 from year 2023 and month 15. DATE automatically adds the extra months to the year. The day remains unchanged.
DATE with Day Overflow
DATE similarly handles day values that exceed the number of days in the month. This example demonstrates this correction.
| A | B | C | D |
|---|---|---|---|
| 2023 | 2 | 30 | =DATE(A1, B1, C1) |
The table shows how DATE handles day values exceeding the month's days. February 2023 has 28 days, so day 30 rolls over to March.
=DATE(2023, 2, 30)
This formula creates March 2, 2023 from February 30, 2023. DATE adds the extra 2 days to February 28, rolling into March. The month increments as needed.
DATE with Negative Values
DATE can handle negative month and day values, counting backward from the first day of the specified month or year. This example shows this behavior.
| A | B | C | D |
|---|---|---|---|
| 2023 | 5 | -5 | =DATE(A1, B1, C1) |
This table demonstrates DATE's handling of negative day values. A negative day counts backward from the first day of the specified month.
=DATE(2023, 5, -5)
This formula creates April 25, 2023, which is May 1 minus 6 days. Excel treats day 0 as the last day of the previous month and counts further negative days backward from there. Similar logic applies to negative months.
DATE in Date Calculations
DATE is often used in date arithmetic to add or subtract days, months, or years from a date. This example shows adding months to a date.
| A | B | C |
|---|---|---|
| 1/15/2023 | 6 | =DATE(YEAR(A1), MONTH(A1)+B1, DAY(A1)) |
The table shows how to use DATE with other date functions to perform date arithmetic. This formula adds 6 months to the date in A1.
=DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))
This formula takes the date in A1 (January 15, 2023) and adds 6 months, resulting in July 15, 2023. DATE automatically handles month overflow if needed.
DATE with Dynamic Arrays
In modern Excel, DATE can work with dynamic arrays to generate date sequences. This example creates a series of dates.
=DATE(2023, 1, SEQUENCE(31))
This formula generates all dates in January 2023 (1/1/2023 through 1/31/2023). The SEQUENCE function provides the day numbers, and DATE converts them to valid dates. This creates a dynamic array of dates.
DATE with Other Functions
DATE is often combined with other functions like TODAY to create relative dates. This example shows creating a date 90 days from today.
=DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())+90)
This formula calculates the date 90 days from today. It extracts year, month, and day from TODAY(), adds 90 to the day, and DATE handles any overflow automatically. The result is a future date.
DATE for Last Day of Month
A common technique uses DATE to find the last day of a month by specifying day 0 of the next month.
=DATE(2023, 3, 0)
This formula returns February 28, 2023 (or 29 in leap years). Day 0 of March means the last day of February. This technique works for any month to find its last day.
DATE Compared with EDATE and EOMONTH
The DATE function builds a date from parts, while
EDATE shifts a date by a number of months and EOMONTH
returns the last day of a month. All three return serial numbers, but they
solve different problems.
=EDATE(DATE(2023, 1, 15), 6) ' July 15, 2023 =EOMONTH(DATE(2023, 2, 10), 0) ' February 28, 2023
DATE(YEAR(A1), MONTH(A1)+6, DAY(A1)) and EDATE(A1, 6)
often agree, but they differ at month ends. Adding months with DATE
can overflow into the next month, whereas EDATE clamps to the last
valid day. Use EDATE when you want a clean month shift and
DATE when you want to control each component.
DATE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Year is less than 0 or greater than 9999 | #NUM! error | Use a four-digit year between 1900 and 9999 |
| Result rolls outside the supported range | #NUM! error | Reduce the month or day offset |
| An argument is non-numeric text | #VALUE! error | Convert the text to a number first |
| Year typed with two digits | Date appears in the 1900s | Enter the full four-digit year |
| Cell shows a number such as 45061 | Serial number instead of a date | Apply a date number format with Ctrl+1 |
| Month or day overflows | Date lands in a later month or year | Expected behavior; supply valid values to avoid it |
| Day set to 0 | Returns the last day of the previous month | Intentional technique; use EOMONTH for clarity |
The DATE function is the foundation of reliable date work in Excel.
It assembles a valid date from separate year, month, and day values, rolls
out-of-range components forward or backward, and returns the serial number that
every other date function understands. Once you know the serial-number system,
the 0-1899 year rule, and the way overflow behaves, you can build dynamic dates,
parse text into dates, and combine DATE with YEAR,
MONTH, DAY, EDATE, and
EOMONTH without surprises. Master these fundamentals and date
manipulation in Excel becomes predictable and dependable.
Author
List all Excel Formulas.