The DATE function builds a valid Excel date from three separate numbers: year, month, and day. It safely handles overflow and avoids text-date problems, so calculations and sorting always work correctly.
=DATE(year, month, day)What does DATE do?
The DATE function takes three numbers — a year, a month, and a day — and turns them into a real Excel date. Instead of typing a date as text and hoping Excel understands it, you supply the parts and Excel assembles a proper date value that you can sort, filter, format, and calculate with.
This matters because Excel treats text like "31/02/2024" or "March 5" inconsistently depending on regional settings. A real date value has no such ambiguity. DATE also rolls over automatically: month 13 becomes January of the next year, and day 32 becomes the first day of the next month, which is exactly what you want when adding months or days to a starting point.
A typical situation: a finance team receives a report where year, month, and day sit in three separate columns. They need one clean date column to sort invoices and calculate payment due dates. =DATE(A2,B2,C2) solves it in one step, and the result behaves like any other date in Excel.
When to use DATE
- Combining separate year, month, and day columns into one date
- Calculating a due date by adding days or months to a start date
- Building the first or last day of a month for reporting periods
- Creating a date from text or numbers extracted from imported files
- Generating a series of dates for schedules and timelines
- Fixing dates that Excel stored as text and will not sort correctly
DATE syntax
=DATE(year, month, day)| Argument | Required? | What to enter |
|---|---|---|
year | Required | The four-digit year, such as 2024. Values between 0 and 1899 are added to 1900, so 24 means 1924 — always enter the full year. |
month | Required | The month number from 1 to 12. Values above 12 roll into the next year, and 0 or negative values roll backwards. |
day | Required | The day number from 1 to 31. Values beyond the end of the month roll into the following month. |
What it returns: A valid Excel date serial number, displayed as a date according to the cell's number format. For example, =DATE(2024,3,15) returns 15 March 2024.
DATE examples
1 Build a simple date from three numbers
The goal is to turn three plain numbers into one real date. The formula =DATE(B2,C2,D2) reads the year from B2, the month from C2, and the day from D2, then assembles them into a date value. Excel returns 15 March 2024, which can be sorted and formatted like any date. If the cell shows a number such as 45366, apply a Date number format.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Label | Year | Month | Day | |||
| 2 | Start | 2024 | 3 | 15 | 2024-03-15 | ||
| 3 | Review | 2024 | 6 | 1 | |||
| 4 | Launch | 2025 | 1 | 10 | |||
| 5 |
2 Add days to a start date
Here the goal is a follow-up date that is a fixed number of days after a start date. The formula =DATE(YEAR(A2),MONTH(A2),DAY(A2)+B2) extracts the parts of the start date, adds the days in B2 to the day part, and rebuilds the date. Because DATE rolls over, adding 20 days to 25 March 2024 correctly returns 14 April 2024. The result is a true date that can be used in further calculations.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start date | Days to add | Client | |||
| 2 | 2024-03-25 | 20 | Northwind | 2024-04-14 | ||
| 3 | 2024-04-10 | 7 | Contoso | |||
| 4 | 2024-05-30 | 45 | Fabrikam | |||
| 5 |
3 Find the last day of a month
The goal is the final day of a reporting month, whatever its length. The formula =DATE(B2,C2+1,0) uses month C2 plus one and day 0. Day 0 means the day before the first of the next month, which is the last day of the current month. For February 2024 it returns 29 February 2024, correctly handling the leap year. This trick avoids needing to know whether the month has 28, 30, or 31 days.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Report | Year | Month | |||
| 2 | Q1 close | 2024 | 3 | 2024-03-31 | ||
| 3 | Q2 close | 2024 | 6 | |||
| 4 | Leap check | 2024 | 2 | |||
| 5 |
4 Calculate an invoice due date from payment terms
A business needs a due date that is a number of months after the invoice date. The formula =DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)) takes the invoice year, adds the payment-term months to the invoice month, and keeps the same day. DATE rolls month 14 into February of the next year, so 15 November 2024 plus 3 months returns 15 February 2025. The result is a clean date for chasing payments or feeding an ageing report.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice date | Terms (months) | Customer | |||
| 2 | 2024-11-15 | 3 | Adatum | 2025-02-15 | ||
| 3 | 2024-12-01 | 1 | Tailspin | |||
| 4 | 2025-01-20 | 2 | Litware | |||
| 5 |
5 Build the first day of the next month for a schedule
The goal is the first day of the month following a given date, useful for billing cycles and rosters. The formula =DATE(YEAR(A2),MONTH(A2)+1,1) takes the year from A2, adds one to the month, and fixes the day at 1. For 18 September 2024 it returns 1 October 2024. Because the day is fixed, it works even when the current month is December, rolling into January of the next year.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Current date | Plan | Owner | |||
| 2 | 2024-09-18 | Billing | Rota team | 2024-10-01 | ||
| 3 | 2024-12-05 | Billing | Rota team | |||
| 4 | 2025-02-27 | Billing | Rota team | |||
| 5 |
Common DATE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the arguments is text that Excel cannot read as a number, such as a month name like "March" or a cell containing spaces. | Use numeric month values (1–12) or convert the text with VALUE or MONTH before passing it to DATE. |
| #NUM! | The resulting date falls before 1 January 1900 or after 31 December 9999, which Excel cannot represent. | Check the year argument; a small year such as 24 becomes 1924, and very large month or day offsets can push the date out of range. |
| A number such as 45366 instead of a date | The formula is correct but the cell has the General number format, so Excel shows the underlying date serial number. | Select the cell and apply a Date format from the Number Format dropdown or the Format Cells dialog. |
| Wrong year, such as 1924 instead of 2024 | Years 0 to 1899 are added to 1900 by Excel, so entering 24 gives 1924. | Always enter the full four-digit year, or add 2000 to a two-digit year with DATE(B2+2000,C2,D2). |
| #NAME? | The function name is misspelled, for example =DAT(...), or the formula uses a non-standard function. | Type =DATE( and let Excel suggest the function, then complete the arguments. |
Tips for using DATE
- Always enter four-digit years; two-digit years below 1900 are shifted by Excel and give surprising results.
- Use day 0 to get the last day of the previous month and month 13 to get January of the next year — DATE rolls over safely.
- Combine DATE with YEAR, MONTH, and DAY to rebuild dates after adding offsets, as shown in the due-date example.
- Format the result cell as a date; otherwise you may see a five-digit serial number instead of a readable date.
- When importing data, use DATE to convert separate year, month, and day columns into real dates so sorting and filters work.
- Test leap years and month-end dates; DATE handles them correctly, which is a good reason to prefer it over manual text dates.
Frequently asked questions
What is the difference between DATE and DATEVALUE?
DATE builds a date from three numbers, while DATEVALUE converts a text string such as "15/03/2024" into a date. Use DATE when you have separate parts, and DATEVALUE when the date is stored as text.
Why does DATE(24,3,15) return 1924 instead of 2024?
Excel adds 1900 to any year between 0 and 1899. Enter the full year, 2024, or add 2000 to a two-digit year in the formula.
Can DATE handle month 13 or day 32?
Yes. DATE rolls over: month 13 becomes January of the next year, and day 32 becomes the first day of the next month. This is useful for adding months and days.
Why does my DATE formula show a number like 45366?
The formula is working, but the cell is formatted as General. Apply a Date format to the cell to display it as a date.
Does DATE work with times?
DATE returns only a date, with no time part. To add a time, combine it with TIME, for example =DATE(2024,3,15)+TIME(9,0,0).
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. DATE reference