DATE Function in Excel and Google Sheets
Purpose
Create a date value from separate year, month, and day numbers
Return value
A date serial number that displays as a date when the cell is formatted as a date
DATE is the function that constructs a proper date value from its three components. Before I knew it, I was concatenating text strings to make things look like dates — which broke every date function I tried to use on them. DATE creates a real date that DATEDIF, TODAY, WORKDAY, and all the other date functions recognise and work with. Its everyday uses are building a date from year, month, and day columns imported separately from a database, finding the first or last day of any month dynamically, and adding a fixed number of months to a date while letting DATE handle the month-overflow cases automatically. That overflow behaviour is one of DATE's hidden superpowers — DATE(2024,13,1) does not error, it correctly returns January 1 2025, which makes month arithmetic reliable without any manual boundary checking.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=DATE(year, month, day)
Arguments
| Argument | Required | Description |
|---|---|---|
| year | Required | The year component as a four-digit number. Two-digit years are interpreted based on system settings and can produce unpredictable results — always use four digits. |
| month | Required | The month as a number from 1 (January) to 12 (December). Values outside this range are allowed: 13 rolls to January of the next year, 0 returns December of the previous year, and negative values subtract further back. |
| day | Required | The day of the month. Values outside the valid day range for the month are handled by overflow: 32 in a 31-day month rolls to the first day of the next month. Day 0 returns the last day of the previous month. |
How it works
DATE returns a date serial number — the same underlying number system that all dates in Excel and Google Sheets use, and the same format accepted by DATEDIF and the calculate age formulas that depend on valid date inputs. Format the result cell as a date to display it correctly; without date formatting it shows as a number like 45366. The three arguments accept formula results as well as literal numbers, so DATE can be combined with YEAR, MONTH, and DAY to deconstruct an existing date and reconstruct it with modifications. DATE(YEAR(A2),MONTH(A2)+1,1) gives the first day of the following month regardless of how many days the current month has. DATE(YEAR(A2),MONTH(A2),0) gives the last day of the current month by using day 0 of the next month. Month arithmetic with DATE is reliable across month-end boundaries — adding 3 months to October correctly produces January of the next year with no manual year-rollover logic needed. To calculate differences between dates, use DATEDIF or simple subtraction.
Examples
1
Build a valid date from separate Year, Month, and Day columns in a database import.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Month | Day | Date |
| 2 | 2024 | 3 | 15 | =DATE(A2,B2,C2) |
| 3 | 2024 | 12 | 1 | 2024-12-01 |
| 4 | 2025 | 1 | 31 | 2025-01-31 |
| 5 | 2023 | 6 | 20 | 2023-06-20 |
| 6 | 2024 | 9 | 5 | 2024-09-05 |
Row 2: 2024-03-15
— DATE combines year 2024, month 3, and day 15 into the date March 15 2024 as a proper date serial number.
Format the result column as a date — without date formatting the cell may display a serial number like 45366. The DATE result works correctly with all date functions unlike text strings that look like dates.
2
Find the first day of the month for any date by using DATE with YEAR, MONTH, and a fixed day of 1.
| A | B | |
|---|---|---|
| 1 | Date | First of Month |
| 2 | 2024-03-15 | =DATE(YEAR(A2),MONTH(A2),1) |
| 3 | 2024-07-28 | 2024-07-01 |
| 4 | 2025-01-31 | 2025-01-01 |
| 5 | 2023-12-10 | 2023-12-01 |
| 6 | 2024-09-05 | 2024-09-01 |
Row 2: 2024-03-01
— YEAR extracts 2024 and MONTH extracts 3 from March 15 2024. DATE(2024,3,1) constructs March 1 2024 — the first day of that month.
This pattern works for any date regardless of the month length. The equivalent for the last day of the month is DATE(YEAR(A2),MONTH(A2)+1,0) — day 0 of the next month always equals the last day of the current month.
3
Add 3 months to a date with DATE, allowing the function to handle month overflow and year rollover automatically.
| A | B | |
|---|---|---|
| 1 | Start Date | Plus 3 Months |
| 2 | 2024-01-15 | =DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)) |
| 3 | 2024-07-20 | 2024-10-20 |
| 4 | 2024-10-01 | 2025-01-01 |
| 5 | 2024-11-10 | 2025-02-10 |
| 6 | 2024-03-31 | 2024-07-01 |
Row 2: 2024-04-15
— For 2024-01-15, adding 3 to the month gives month 4, so DATE returns 2024-04-15. DATE also handles rollover automatically: October plus 3 months becomes January of the next year, and March 31 rolls to July 1 because June has only 30 days — day 31 in a 30-day month overflows to the first day of the next month.
The October example shows year rollover: MONTH(October)+3 = 13, and DATE handles month 13 by rolling into January of the next year. The March 31 example rolls to July 1 because June has only 30 days — day 31 in June overflows to July 1.
Common use cases
1. Build a valid date from three separate year, month, and day columns imported from a database or CSV
=DATE(A2,B2,C2)
2. Find the first day of the month for any date to group transactions into monthly buckets
=DATE(YEAR(A2),MONTH(A2),1)
3. Find the last day of any month without knowing how many days it has
=DATE(YEAR(A2),MONTH(A2)+1,0)
4. Add a fixed number of months to a contract start date to calculate the renewal or expiry date
=DATE(YEAR(A2),MONTH(A2)+12,DAY(A2))
5. Construct a fiscal year start date from a year number for financial reporting periods
=DATE(A2,4,1)
Common errors
Result displays as a number instead of a date
The output cell is not formatted as a date. DATE returns a serial number that must be displayed in a date format to look like a date.
Fix: Select the result cell, press Ctrl+1, and choose a date format from the Number category. Alternatively set the column format to Date before entering the formula.
#VALUE! error
One of the arguments contains text rather than a number — for example, the month column contains the word March rather than the number 3.
Fix: Ensure all three arguments are numeric. Use MONTH(DATEVALUE(A2)) to extract the month number from a text date string if the source data is in text format.
#NUM! error
The year argument is less than 0 or greater than 9999, or the resulting date falls outside the valid date range.
Fix: Check the year argument is a valid four-digit year between 1 and 9999. Two-digit year inputs can produce unexpected results depending on system settings.
Tips and variations
Use day 0 to find the last day of any month
DATE(year,month+1,0) returns day 0 of the following month which is always the last day of the current month — whether it has 28, 29, 30, or 31 days. This is the cleanest last-day calculation without hardcoding month lengths.
=DATE(YEAR(A2),MONTH(A2)+1,0)
Add months reliably using DATE with month arithmetic
DATE(YEAR(A2),MONTH(A2)+N,DAY(A2)) adds N months with automatic rollover when the result exceeds December. For adding days, simple addition works: =A2+30. For adding months, DATE is the correct approach because adding 30 days to January 31 gives March 2, not February 28.
=DATE(YEAR(A2),MONTH(A2)+3,DAY(A2))
Always use four-digit years to avoid ambiguity
Two-digit years in the year argument are interpreted based on operating system settings — 24 might mean 1924 or 2024 depending on the threshold setting. Always pass four-digit years like 2024 to DATE to guarantee the correct century.
=DATE(2024,MONTH(A2),DAY(A2))
Excel vs Google Sheets
Excel vs Google Sheets
DATE works identically in Excel 2003 and newer and every version of Google Sheets. The syntax, serial number date system, and month-overflow behaviour are the same in both applications. Formulas copy between them without changes.
Related reading
Related Functions
Frequently asked questions
DATE creates a valid date value from separate year, month, and day numbers. It is most useful when date components are stored in separate columns, when you need to construct a date from formula results, or when you need to add months to a date while letting Excel handle month-overflow automatically.
Typing a date directly into a cell stores whatever Excel or Google Sheets interprets from your regional settings, which can vary and cause portability issues. DATE(2024,3,15) always creates the precise date March 15 2024 regardless of locale or regional date format settings, making it reliable in shared files.
DATE handles overflow automatically. DATE(2024,13,1) where month 13 exceeds December rolls over to January 2025. DATE(2024,3,32) where day 32 exceeds March rolls to April 1 2024. Negative values subtract from the date: DATE(2024,3,0) returns the last day of February 2024. This overflow behaviour is intentional and useful for date arithmetic.
Wrap the date in YEAR, MONTH, and DAY to extract its components, then add the number of months to the MONTH argument: =DATE(YEAR(A2),MONTH(A2)+3,DAY(A2)) adds exactly three months. DATE handles the overflow when adding months takes you past December — MONTH+13 becomes January of the following year automatically.
Yes. DATE works identically in both applications. The syntax, argument handling, overflow behaviour, and the serial number date system are the same. The returned serial number displays as a date when the cell is formatted as a date format, which is also consistent across both applications.