Quick Answer

DATE Function

Assembles a date from year, month, and day values.

✓ Excel✓ Google SheetsExcel All versions

Syntax

DATE

(year, month, day)

Parameters

Parameter	Description	Required
year	The year component. In Excel, values 0-1899 are added to 1900 (0 becomes 1900, 119 becomes 2019); values 1900-9999 are used as-is.	Required
month	The month component, 1-12. Values outside that range roll over into adjacent years (13 = January of the next year, 0 = December of the previous year).	Required
day	The day component, 1-31. Values beyond the length of the month roll into the next month (day 32 of January becomes February 1).	Required

Basic Example

Combine separate cells into a real date

=DATE(2024, 1, 15)

Result45305

Serial 45305 corresponds to 2024-01-15. Apply a date number format to display it as a calendar date.

Advanced Examples

Example 1: Month overflow rolls into next year

A user mistakenly enters month 13

DATE carries the overflow into the following year

=DATE(2024, 13, 1)

Result: 45657

Month 13 is treated as January of the next year, so the result is 2025-01-01 (serial 45657).

Example 2: Day overflow rolls into next month

Building end-of-period dates dynamically

Day 32 of a 31-day month spills into the next month

=DATE(2024, 1, 32)

Result: 45322

January has 31 days, so day 32 becomes 2024-02-01 (serial 45322).

How DATE Works

DATE validates the three integer inputs and converts them into an Excel date serial, the count of days since 1900-01-01 (serial 1). When any component exceeds its normal range, Excel carries the overflow into the next higher unit, so month and day values roll forward automatically.

Enter the year

Provide the four-digit year (or a value Excel will offset into the 1900-9999 range).

Enter the month and day

Provide month and day integers; out-of-range values roll over.

Format as date

Apply a date number format so the serial displays as a calendar date.

Important Notes & Limitations

Excel only supports dates from 1900-01-01 onward; earlier dates return #VALUE!.
Two-digit or small year values are offset by 1900, which can be confusing (always use 4-digit years).
The result is a serial number and must be date-formatted to be readable.

Common Errors & Fixes

#VALUE!A non-numeric or out-of-supported-range argument (e.g. a year before 1900).

Fix: Use a numeric year between 1900 and 9999.

Unexpected yearSupplying 2-digit years like 24, which Excel reads as 1924.

Fix: Always use four-digit years such as 2024.

Download Practice File

Practice DATE with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the DATE function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Why does my DATE result show a number like 45305?

Excel stores dates as serial numbers; format the cell as a date to see 2024-01-15.

What happens if I use month 13?

It overflows into the next year: DATE(2024,13,1) returns 2025-01-01.

Does DATE work with cell references?

Yes, e.g. =DATE(A1,B1,C1) builds a date from three cells.