The DATE function

Builds a date from a year, month and day. Months and days outside the normal range roll over: month 14 is February next year.

How to use DATE

DATE builds a date from three numbers: year, month and day. It works like snapping a date together from three building blocks instead of typing it as text, so Excel knows exactly which number is the month and which is the day, whatever the regional settings.

Use it when a date arrives in pieces, such as three columns with year, month and day from another system: =DATE(A2,B2,C2). Use it to move forward from an existing date: =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)) gives the same day next month. And use it for fixed dates inside a formula, like =IF(A2<DATE(2024,1,1),"Before","After").

The month and day may go outside their normal range. Month 14 becomes February of the next year, and day 0 is the last day of the month before, so =DATE(2024,3,0) is Feb 29, 2024. That makes date math easy, but it has a catch: Jan 31 plus one month rolls over into March. For month steps, EDATE or EOMONTH is the safer choice.

DATE returns a number, so give the cell a date format. If you need the date as text, wrap it in TEXT.

Syntax

DATE(year, month, day)
year
The year, such as 2024.
month
The month, 1 to 12 (other values roll over).
day
The day of the month.

Examples

B1=DATE(2024,3,15)-DATE(2024,1,1)
AB
174
B1=MONTH(DATE(2024,14,1))
AB
12

Common DATE mistakes

  • DATE shows a number like 45366 instead of a date

    The formula is right, but the cell is formatted as a number. Give the cell a date format and the date appears.

  • DATE returns 1924 instead of 2024

    Years from 0 to 1899 get 1900 added. Write the full year, as in =DATE(2024,3,5), or add 2000 yourself: =DATE(2000+A2,3,5).

  • A date from another system does not work in calculations

    It is probably stored as text, often left-aligned. Rebuild it with DATE from its parts. For text like 15-03-2024 in A2, use =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).

  • Adding 30 days does not land on the same day next month

    Months have different lengths, so 30 days drifts. Use =EDATE(A2,1) for the same day next month, or =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)) if the day is 28 or less.

Read the guide

Questions

How do I add months to a date?

Use =EDATE(A2,3) to add three months. Building it with DATE and MONTH(A2)+3 works too, but a day that does not exist in the new month, such as the 31st, rolls into the month after.

How do I get the last day of a month?

=EOMONTH(A2,0) gives the last day of the month in A2. With DATE you can use day 0 of the next month: =DATE(2024,3,0) returns Feb 29, 2024.

How do I turn year, month and day columns into one date?

Put the three cells into DATE in that order: =DATE(A2,B2,C2).

How do I count the days between two dates?

Subtract them. =DATE(2024,3,15)-DATE(2024,1,1) gives 74. If the result shows as a date, change the cell format to Number.

Related functions