DATE: build dates that Excel can actually calculate
A date that looks right is not always a date value. DATE constructs one from numeric parts, avoiding the ambiguity of strings such as 04/05/2026.
Formula syntax
=DATE(year, month, day)
What goes into it
year
Year component.
month
Month component; Excel can normalize values outside 1–12.
day
Day component; Excel can normalize overflow/underflow.
A quick example first
=DATE(2026,9,12)
Builds the date 12 September 2026.
=DATE(YEAR(A2),MONTH(A2)+1,1)
Builds the first day of the following month.
Build report boundaries without ambiguous text dates
Enter year, month and day as numbers in A2:C4.
| Row | A: Year | B: Month | C: Day |
|---|---|---|---|
| 2 | 2026 | 9 | 30 |
| 3 | 2026 | 9 | 31 |
| 4 | 2026 | 13 | 1 |
| Formula | Expected result / interpretation |
|---|---|
| =DATE(A2,B2,C2) | 30 September 2026 |
| =DATE(A3,B3,C3) | 1 October 2026 |
| =DATE(A4,B4,C4) | 1 January 2027 |
DATE normalizes overflowing month and day values. This is useful for building the first day of the next month, but it means DATE is not a strict validator for an entered calendar date. September 31 becomes October 1 instead of being rejected.
Before you copy this into a real sheet
Use four-digit years and format the result as a date. To validate the components, compare YEAR, MONTH and DAY of the constructed date back to the inputs. For imported text such as 03/04/2026, decide the source day/month order before parsing; changing display format cannot settle that ambiguity. Use an exclusive next-month boundary in timestamp criteria. The serial value also depends on the workbook date system, so do not compare raw date numbers across differently configured workbooks without checking that setting.
Useful habits
- DATE is safer than ambiguous typed date strings in formulas.
- Combine with YEAR/MONTH/DAY to reconstruct or shift dates.
- Remember that dates are stored as serial numbers under the cell formatting.
Where people get caught out
- Passing a two-digit year and assuming the intended century.
- Comparing real dates to text that only looks like a date.
Other functions worth a look
A detail worth remembering
Always check the source year, month and day. DATE can normalize out-of-range month or day arguments, which may conceal a data-entry issue.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.