DATE Function in Excel: Build a Date From Year, Month and Day

📎 This article includes 2 downloadable practice files ↓

Date & TimeLevel: BeginnerAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. DATE in practice
7. Where you will use it
8. Worked example
9. Mistakes people make
10. Related functions
11. Continue learning

DATE creates a real date from separate year, month and day numbers — the reliable way to put dates in formulas.

Syntax

=DATE(year, month, day)

Argument	What it means
year	Four-digit year.
month	1–12 (larger values roll into the next year).
day	1–31 (0 = last day of the previous month).

Examples

Example 1

=DATE(2016, 4, 1)

1-Apr-2016, start of the financial year.

Example 2

=DATE(YEAR(A2), MONTH(A2)+1, 0)

Last day of A2’s month.

Example 3

=DATE(YEAR(A2)-(MONTH(A2)<4), 4, 1)

Start of the Indian financial year for any date.

DATE in practice

Where you will use it

• Build a date from separate year, month, day columns in exports
• Make month-start dates for reports
• Avoid regional date typing problems in formulas

Worked example

Year	Month	Day	Formula	Result
2026	10	4	=TEXT(DATE(A2,B2,C2),"dd-mmm-yyyy")	04-Oct-2026
2027	2	29	=TEXT(DATE(A3,B3,C3),"dd-mmm-yyyy")	01-Mar-2027
2026	13	1	=TEXT(DATE(A4,B4,C4),"dd-mmm-yyyy")	01-Jan-2027

Results calculated in Excel. Shown with TEXT so the date is readable.

Mistakes people make

Mistake	What to do instead
Typing dates as text inside formulas	Use DATE(2026,4,1) instead of "01/04/2026"
Surprised by rollovers	DATE rolls over: month 13 becomes January next year, 29 Feb in a non-leap year becomes 1 March

Related functions

YEAR · EOMONTH · EDATE · TEXT

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Date & Time guides →

TIMEVALUE Function in Excel: Text Time to Real TimeDateLevel: IntermediateAvailable in: Excel 2007+TIMEVALUE turns text like "2:45 PM" into a real time…DATEVALUE Function in Excel: Convert Text Dates Into Real DatesDATEVALUE turns a date written as text into a real date number. It follows…MINUTE Function in Excel: Minute From a TimeDateLevel: BeginnerAvailable in: Excel 2007+MINUTE returns 0–59 from a time.Syntax=MINUTE(serial_number)ArgumentWhat it.SECOND Function in Excel: Second From a TimeDateLevel: BeginnerAvailable in: Excel 2007+SECOND returns 0–59. Mostly used to strip seconds from machine.

📎 Practice files for this article

⬇ Download all 2 files (ZIP · 37 KB)

• 📗
  Date scenarios workbookMonth maths, financial-year start, working days with holidays — with PASS/FAIL checks and the 2 most common date mistakes.
  ⬇ XLSX · 8 KB
• 📗
  Practice workbook: Dates & times22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.