The Excel DATE function combines three values to create a date. When you specify the year, month, and day, Excel produces a serial number that can then be formatted as a normal looking date.

The typical way to enter a date in Excel is to write the entire date within one cell, but that's inconvenient when you're dealing with lots of information. The DATE function can be useful if the date isn't formatted correctly, like if it's combined with regular text or is spread over multiple cells.

The DATE function is fairly easy to understand and remember. Below is more information about the DATE function and examples on how to use DATE formulas.

The DATE function can be used in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel for Mac 2011, Excel 2010, Excel Starter 2010, and Excel 2007.

## DATE Function Syntax & Arguments

This is how every instance of the date function has to be written for Excel to process it correctly:

=DATE(year,month,day)

**Year**: Enter the year as a number that’s one to four digits in length or enter the cell reference to the location of the data in the worksheet. The*year*argument is required.**Month**: Enter the month of the year as a positive or negative integer from 1 to 12 (January to December) or enter the cell reference to the location of the data. The*month*argument is required.**Day**: Enter the day of the month as a positive or negative integer from 1 to 31 or enter the cell reference to the location of the data. The*day*argument is required.

Here are some things to remember about the DATE function. Examples of some of these rules are shown in the *DATE Function Examples* section below.

### YEAR

- By default, Excel uses the 1900 date system, which means that the DATE function won’t display the year correctly for anything older than 1900.
- Entering
**0**as the*year*value is the same as entering 1900,**1**is equal to 1901,**105**is 2005, etc.

### MONTH

- Exceeding
**12**as the*month*value will add that number of months to the*year*value.**13**, then, adds one year and one month to the date. - Using a negative number as the
*month*value will subtract that number of months, plus one, from the first month of*year*.

### DAY

- If the
*day*value exceeds the number of days that*month*has, the excess days are added to the first day of the next month. - A negative
*day*value subtracts that number of days, plus one, from the first day of*month*.

## DATE Function Examples

=DATE(A2,B2,C2)

This example of the DATE function, seen in the image above, is using *A2* for the year, *B2* for the month, and *C2* for the day.

=DATE(2020,B2,C2)

You can also mix how the data is obtained. In this example, we’re making 2020 the *year *argument, but *month *and *day *is being pulled from other cells.

=DATE(2020,-2,15)

Here, we’re using a negative number in the *month* space. This moves backwards through the year instead of forward, beginning on January 2020 (since the formula includes *2020*). This DATE formula produces *10/15/2019*.

=DATE(2020,1,-5)

Without the negative number, this date would be calculated as 1/5/2020. However, the negative *day* value is subtracting five days (plus one) from 1/1/2020, which produces the date 12/26/2019.

=DATE(2020,19,50)

This example combines a few of the rules mentioned above. The *year *value will increase from 2020 because *month *exceeds 12, and the month that will be calculated will change as well since the *day *value exceeds the number of days in any month. This DATE formula produces 8/19/2021.

=DATE(YEAR(E2)+10,MONTH(E2),DAY(E2))

The Excel DATE function can also be used with other dates, like to add time to an existing date. In this example, we’re wanting to see the date that’s 10 years past an existing date. The existing date is in cell E2, so we need to write this DATE formula in a way that extracts the year, month, and day from E2 but also adds 10 to the *year* value.

=E10-DATE(YEAR(E10),1,0)

Here's a similar example of the DATE function where we're calculating how many days into the year the date in cell E10 is. For example, 1/1/2020 is one day into the year, the 5th of January is five days, and so on. In this example, E10 is 8/4/2018, so the result is *216*.

=DATE(LEFT(E13,4),MID(E13,5,2),RIGHT(E13,2))

If the cell you’re dealing with contains the full date but it’s formatted as text, such as *20200417*, you can use this DATE formula, combined with the *LEFT*,* MID*, and *RIGHT* functions, to convert the cell to a properly formatted date.

What this is doing is extracting the first four digits from the left with *LEFT(E13,4)*, taking two digits from the middle at the fifth character via *MID(E13,5,2)*, and combining it with the last two digits from the right with *RIGHT(E13,2)*. The calculated date is *4/17/2020*.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 5)

The TODAY function can be used with the DATE function to pull information about today. For example, to remind yourself to pay bills every month on the 5th, you can use this DATE formula to automatically add in the current year and month, but then place *5* (or a cell reference) as the *day *value.

## More Information on Date-Related Functions

If the result of the DATE function shows a bunch of numbers instead of a date, you’ll need to format the cell as a date. For example, you might see *43938* instead of a normal looking date. To format the cell as a date, select it and then change the **Number** menu group item to be one of the date formats.

One easy way to add days to a date without using the example shown above, is to use a simple addition formula like this one (*B10* can be the date and *C11* the days you want to add to it):

=B10+C11

For the DATE function to calculate a month from another cell, it has to be written as a number. If the cells you’re working with use the month name, like *January *or *October*, you have to use the MONTH function to convert the text to a number.

Here’s an example where B13 contains the text *June*. This formula produces *6*:

=MONTH(1&B13)