SheetsLessons
Date & Time function

DATE function in Google Sheets

DATE builds a real date from a year, a month and a day. Use it to turn three separate columns into one date, to write a fixed date safely inside a formula, or to do month maths, because DATE rolls over months and days that are out of range.

Syntax

=DATE(year, month, day)
year
The year, written with four digits (2026). Years 0 to 1899 are added to 1900.
month
The month, 1 to 12. 13 becomes January of the next year, and 0 is December of the previous year.
day
The day of the month. 0 is the last day of the previous month.

Practice DATE

Task 1 of 1

Build a real date from the year, month and day in columns A to C. Put it in D2.

D2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

DATE examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABCD
1TaskStartDueHolidays
2Kickoff1/5/20261/9/20261/19/2026
3Design1/12/20262/13/20262/16/2026
4Build2/16/20264/30/20265/25/2026
5Launch5/4/20265/29/2026
Project schedule (column D lists public holidays)
FormulaResultWhat it shows
=DATE(2026, 3, 15)3/15/2026A fixed date.
=DATE(2026, 13, 1)1/1/2027Month 13 rolls into the next year.
=DATE(2026, 3, 0)2/28/2026Day 0 is the last day of the previous month.
=DATE(YEAR(B3), MONTH(B3)+1, 1)2/1/2026The first day of the month after the Design start.
=COUNTIF(C2:C5, ">="&DATE(2026, 4, 1))2Tasks due on or after April 1, 2026.
=DATE(26, 1, 1)1/1/1926A two-digit year means 1926, not 2026.

Tips and common mistakes

  • Inside formulas, DATE(2026, 3, 15) is safer than typing "3/15/2026", because text dates are read according to the spreadsheet's locale and 3/4 can mean March 4 or April 3.
  • Always write four-digit years. DATE(26, 1, 1) is in 1926.
  • If the result shows as a number like 46096, format the cell as a date (Format › Number › Date).

Related functions

YEARThe year of a date.MONTHThe month of a date, 1 to 12.DAYThe day of the month of a date, 1 to 31.DATEVALUEConverts text that looks like a date into a real date.EOMONTHThe last day of the month, a number of months before or after a date.

Learning step by step? DATE is covered in Lesson 39: Dates: DATE, YEAR, MONTH, DAY & WEEKDAY of the free course.