Date & Time

DATE Function in Excel

Creates a date from year, month, and day components.

Syntax

  • =DATE(year, month, day)

Arguments

  • year (required): The year (1900-9999)
  • month (required): The month (1-12)
  • day (required): The day (1-31)

Examples

  • =DATE(2026, 1, 21) - Create specific date - Result: 1/21/2026
  • =DATE(YEAR(A1), MONTH(A1)+1, 0) - Last day of month - Result: End of month
  • =DATE(A1, B1, C1) - Date from separate cells - Result: Combined date

DATE best practices

  • Use DATE to build reliable dates from separate year, month, and day values.
  • Use DATE in criteria formulas to avoid regional date format problems.

Excel DATE function documentation

  • DATE builds a valid Excel date serial from separate year, month, and day arguments.
  • Syntax: =DATE(year, month, day). Avoids regional ambiguity of typed dates (US vs EU).
  • Handles overflow: =DATE(2024, 13, 1) rolls to January 2025 automatically.
  • Related: [EDATE](/functions/edate/), [EOMONTH](/functions/eomonth/), [YEAR](/functions/year/), [TEXT](/functions/text/).

DATE syntax and argument rules

  • year: 1900–9999 (two-digit years follow Excel's 1900/2000 window rules).
  • month: 1–12 normally; values outside roll into adjacent months/years.
  • day: 1–31 normally; day 0 rolls to last day of prior month.
  • All three arguments must resolve to numbers — text years cause #VALUE!.

Step-by-step: build dates from imported columns

  • Step 1 — CSV columns: Year in A, Month in B, Day in C.
  • Step 2 — =DATE(A2, B2, C2) in column D.
  • Step 3 — Format D as Date (yyyy-mm-dd) for readability.
  • Step 4 — Filter #VALUE! rows — non-numeric month/day in source.
  • Step 5 — Alternative from text: =DATEVALUE(A2) when single text date column exists.

DATE vs DATEVALUE vs typing dates

  • DATE — construct from parts; safest for multi-column imports.
  • DATEVALUE — parse one text string per locale settings.
  • Typed date — fast but locale-dependent in shared files.
  • Use DATE in templates shared internationally.

Worked examples to copy

  • First of month: =DATE(YEAR(TODAY()), MONTH(TODAY()), 1).
  • End of month without EOMONTH: =DATE(YEAR(A2), MONTH(A2)+1, 0).
  • Birthday this year: =DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)).
  • Quarter end helper: =DATE(2024, 3*2, 1)-1 for Q2 end (verify with EOMONTH).

People also ask

  • Why DATE #VALUE!? — Non-numeric year, month, or day argument.
  • DATE with month 13? — Excel rolls forward (DATE(2024,13,1) = Jan 2025).
  • DATE vs serial number? — DATE returns serial; format cell as date to display.
  • DATE for leap year Feb 29? — DATE(2024,2,29) valid; DATE(2023,2,29) rolls to Mar 2023.

Common errors

  • Month > 12 rolls to next year
  • Day > month days rolls to next month

Use cases

  • Date construction
  • Date calculations
  • Dynamic dates

Frequently asked questions

  • What happens if month or day is out of range? Excel adjusts automatically. =DATE(2026,13,1) becomes Jan 1, 2027. =DATE(2026,1,32) becomes Feb 1, 2026. This is useful for calculations like 'add 3 months'.
  • How do I get the last day of a month? Use day 0 of next month: =DATE(YEAR(A1), MONTH(A1)+1, 0). Day 0 is the last day of the previous month. Works for any month including February.
  • How do I add months to a date? Use EDATE for exact months: =EDATE(A1, 3) adds 3 months. Or DATE: =DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)) but this may give invalid dates (Jan 31 + 1 month).
  • What years does DATE support? DATE supports years 1900-9999. Years 0-99 are interpreted as 1900-1999 (0=1900, 99=1999). For year 2000+, use full 4-digit year.
  • How do I convert text to date? If text is in standard format, use DATEVALUE: =DATEVALUE("1/21/2026"). For non-standard formats, parse with DATE: =DATE(RIGHT(A1,4), LEFT(A1,2), MID(A1,4,2)).

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use DATE

  • Date construction — common Date & Time scenario for DATE.
  • Date calculations — common Date & Time scenario for DATE.
  • Dynamic dates — common Date & Time scenario for DATE.

DATE in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • DATE syntax: =DATE(year, month, day)
  • year (required): The year (1900-9999)
  • month (required): The month (1-12)
  • day (required): The day (1-31)
  • Confirm DATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm DATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • YEAR (/functions/year/): Returns the year of a date as a four-digit number.
  • MONTH (/functions/month/): Returns the month of a date as a number from 1 to 12.
  • DAY (/functions/day/): Returns the day of a date as a number from 1 to 31.
  • TODAY (/functions/today/): Returns the current date. Updates automatically when the worksheet recalculates.
  • DATEVALUE (/functions/datevalue/): Converts a date stored as text to a serial number that Excel recognizes as a date.

Common problems and fixes

  • Why Is SUMIF Not Working? (/problems/sumif-not-working/): Check the criteria syntax, make range and sum_range the same shape, and confirm that numbers and dates are real Excel values.

Errors to watch for

  • Month > 12 rolls to next year — review causes on linked error pages in the directory.
  • Day > month days rolls to next month — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter DATE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

DATE worked examples to copy

  • =DATE(2026, 1, 21) — Create specific date. Expected result: 1/21/2026.
  • =DATE(YEAR(A1), MONTH(A1)+1, 0) — Last day of month. Expected result: End of month.
  • =DATE(A1, B1, C1) — Date from separate cells. Expected result: Combined date.

DATE reference summary for crawlers and offline review

  • DATE belongs to the Date & Time category in Excel. Creates a date from year, month, and day components.
  • Full syntax: =DATE(year, month, day). Open /functions/date/ for parameters, FAQs, and related pages.
  • Common mistakes: Month > 12 rolls to next year; Day > month days rolls to next month
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.