Software & Apps MS Office How to Use the Excel DATE Function The help you need to create valid dates from data by Tim Fisher General Manager, VP, Lifewire.com Tim Fisher has 30+ years' professional technology support experience. He writes troubleshooting content and is the General Manager of Lifewire. our editorial process Facebook Twitter LinkedIn Tim Fisher Updated on June 15, 2020 MS Office Excel Word Powerpoint Outlook Tweet Share Email 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 on how to use it in your formulas. The DATE function can be used in every version of Excel. 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 other important things to know about the year, month, and day arguments: 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 Below are a number of real-world formulas that use the DATE function: Year, Month, and Day in Other Cells =DATE(A2,B2,C2) This example of the DATE function is using A2 for the year, B2 for the month, and C2 for the day. Year in Formula and Month & Day in Another Cell =DATE(2020,A2,B2) 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. Subtract Months Using Negative Month Argument =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. Subtract Days Using Negative Day Argument =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. Large Day & Month Arguments =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. Add 10 Years to Date in Another Cell =DATE(YEAR(A2)+10,MONTH(A2),DAY(A2)) 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 formula in a way that extracts the year, month, and day from E2 but also adds 10 to the year value. Calculate Number of Days Into the Year =A2-DATE(YEAR(A2),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. Convert Date as Text to Properly Formatted Date =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,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(A2,4), taking two digits from the middle at the fifth character via MID(A2,5,2), and combining it with the last two digits from the right with RIGHT(A2,2). The calculated date is 4/17/2020. See our articles on using Excel's LEFT, RIGHT, and MID functions for more information. This Year & Month on a Specific Day =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. Calculate Date When Month is Text =DATE(A2,MONTH(1&B2),C2) Sometimes a date includes the text version of the month, like June. Since Excel doesn't understand this as a number, you have to convert it to one with the MONTH function. We've embedded this directly in the DATE formula, in the month position, as MONTH(1&B2). Fixing Dates That Don't Look Like Dates 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 a large number like 43938 instead of a normal looking date, as in this screenshot below: To reformat the cell, select it, choose the drop-down menu from the Number group item, and then pick one of the date formats.