Excel is an excellent spreadsheet tool for performing various complex tasks, from critical calculations to data analysis. It allows you to easily organize and analyze huge datasets by date.
Many built-in functions, such as DATE, TEXT, DATEVALUE, and more, are available to complete the job.
Whether you’re tracking project deadlines, managing schedules, or analyzing trends over time, these functions in Excel can be beneficial.
Welcome to this guide, where we will explore how to use the DATE formula in Excel using various examples. Read until the end because we will uncover the DATE function syntax, examples, and errors you may encounter while using the function.
Explained: The DATE Formula in Excel
The DATE function in Excel is used to create a date from the year, month, and day available in separate cells.
Simply put, you can use the function when there are separate columns for year, month, and day, and you want to combine them to form a single date.
The General Syntax for the DATE function is as follows,
=DATE(year,month,day)
All three arguments of the DATE function are self-explanatory. You need to replace them with the date’s year, month, and day components. Note that you can manually enter the values or select the cell reference containing the same for all three arguments of the DATE function.
Note that all the decimal values (both higher and lower) will be rounded to the nearest integer number. For example, the function will consider the “10.43” as “10” while generating the month.
Download the Example Excel Sheet
Please consider downloading the following Excel spreadsheet that contains the datasets and tables we used to demonstrate the working of the DATE function in this article.
By practicing what we discuss in the following section, you will become an expert at organizing datasets by date.
If you have your own Excel ready with the required dataset, you can skip downloading the above file.
How to use the DATE function in Excel
The DATE function returns a date in a compatible format based on the year, month, and day values specified by the users.
Consider the following example where we have club member names in one column and their birth year, month, and date in the three separate columns.
Note that,
- The year is in column B
- The month is in Column C
- The Day is in the Column D
Our task is to combine the year, month, and date available across separate columns and get each club member’s birthdate.
Here are the steps,
- Open the desired Excel spreadsheet
- Add a new column to the table as “Date”, as shown below,
- Select the first cell below the column header “Date”
- Type “=DATE”
- Click on the first option from the popup or press “Tab” on your keyboard
- In place of the “year” argument, we will provide the cell reference as “B2” as it contains the year
- Press “,” key to move to the next argument
- For the “month” argument, we will put the cell reference as “C2” as it holds the month
- Press “,” key
- In place of the “day” argument, we will provide the cell reference as “D2”
- End the formula using the closing parenthesis “)” on your keyboard
- Press the “Enter” key to see the result
The final formula would look like below,
=DATE(B2,C2,D2)
After following the above steps, Excel will instantly create a date using the values from the cells B2 (year), C2 (month), and D2 (day). The date is represented in the United States date format as month, day, and followed by year (mm/dd/yyyy).
Now, let us apply this formula to the rest of the cells.
Go to the cell (E2) where we have used the formula by following the above steps. Hover to the lower-rightmost corner of the cell until you see the “+” icon. Now, click on that icon and drag the formula for the rest of the cells.
I am experiencing #VALUE! Error
There are scenarios when you may encounter a ”#VALUE!” error. This can be because the DATE function works only with the integer values. This function is not compatible with text strings.
Refer to the above image. You will see the error “#VALUE!” because the DATE function expects number values instead of the text string “September” in the third column.
The solution for the above problem is to specify the month number representing the month “September” as “9”.
I am experiencing #NUM! Error
You may experience this error if you have an invalid year entry. The DATE function accepts only four digits in the place of the “year” argument.
Here, we have put the number “2” at the end of “1992” to make it look like “19922.” As shown above, it has become a five-digit number.
As it becomes a five-digit number, the DATE function will not accept it. To avoid such errors, make sure to enter a valid four-digit year.
Q. What if I put a number for the Month argument beyond 12?
If the specified month value is beyond 12, the DATE function will add the months beyond 12 to the date.
Consider the following example, in which we have replaced the month number from “09” to “15.”
Here, the desired output is 9/28/1992. However, as we changed the month number to 15, the outcome is 3/28/1993.
It works the same way for days as well. If you put anything beyond 31 or 28 for February and 30 for the specific months, the function will add these additional days and return the results.
What is another method to access the DATE function in Excel?
You can access the DATE function in Excel in two different ways.
In the first method, you simply select the desired cell and start typing “=date”, then select the first option from the popup.
If you don’t prefer manually typing the formulas in Excel, here is the second method to access the DATE function in Excel.
- Open the desired Excel spreadsheet
- Click on the cell where you wish to insert the formula
- Hover to the main menu and click on the “Formulas” tab
- Choose the “Insert function” option from the list
- An “Insert function” window will be opened as shown below,
- Select the formula that you want to insert from the available options or search for it in the Search bar at the top
- Once you select the formula, you will be prompted to enter the argument values as shown below,
Both methods are pretty straightforward. If you are a beginner, insert the formula using the main menu method. Or else, you can select the cell and start typing the formula immediately.
How to change the Date format in Excel
As mentioned, the date format obtained using the DATE function is mm/dd/yyyy.
However, if you wish to change the format according to your choice, follow any of the methods discussed below.
METHOD #1: Right-click Method
It includes a straightforward process that allows you to display dates in various formats to suit your needs.
Let’s begin,
- Open the desired spreadsheet
- Select the cells containing the dates
- Right-click on the selected cells
- Click on “Format Cells”
- A “Format Cells” dialogue box will be opened, as shown below
(You can use the keyboard shortcut “CTRL + 1” to open the “Format cells” dialogue box) - Click on the “Number” tab
- From the list of categories on the left, select the “Date” option
- After you select “Date”, you will see different date formats displayed on the right. Choose the date format that you prefer
- Press the “OK” button to close the popup
If you don’t find a format that suits your needs, you can create a custom date format by selecting “Custom” from the list of categories on the left. Then, enter the custom date format in the “Type “ box, as shown in the screenshot below.
METHOD #2 – Main Menu Method
This is another method you can use to change the date format of the cells.
Here are the steps,
- Open the desired spreadsheet
- Select the cells containing the dates
- Go to the “Home” tab of the main menu
- Find the “Number” section and click the dropdown as shown below
- Next, you need to choose the “More Number Formats” option
- In the “Format Cells” dialogue box, locate the “Number” tab at the top, and on the left side, under “Category”, select the “Date” option
- You can choose the date format you prefer from the list
- Once you finish, click on “OK” to apply the changes
Both methods use the Format Cells dialogue box to change the date formatting. The only difference is how you access the Format Cells dialogue box.
How to check a valid date in Excel
Checking for a valid date in Excel is essential to ensure accurate and error-free calculations in future.
Excel provides several functions and methods for validating dates. Let’s examine some of them.
METHOD #1 – Check the alignment of the Date
Valid dates are always aligned to the right side of the cell, as shown in the image below.
Here, the dates in the first three cells are valid, whereas those in the fourth and fifth rows are invalid.
If Excel does not recognize the date formats, they are shifted to the left side of the cell and treated as a regular text string.
METHOD #2 – Using the DATEVALUE Function
The DATEVALUE is one of the simple and useful functions in Excel. It returns a five-digit serial number for the given date. The general syntax of this formula is as follows,
=DATEVALUE(date_string)
Here, the “date_string” is the date in text format or the cell reference containing the date string you want to convert.
Here is an example of the DATEVALUE function,
As the first three dates are valid, the DATEVALUE function successfully returned serial numbers for them.
On the other hand, the dates in cells B5 and B6 are invalid, so the function has returned the errors as shown below.
Method #3 – Using the “ISNUMBER” Function
You can use the ISNUMBER function to check if the cell values represent valid dates.
The ISNUMBER function in Excel is an excellent tool for determining whether a cell contains a numeric value. Since Excel stores dates as serial numbers, this value can include dates.
The general syntax for the ISNUMBER function is as follows,
=ISNUMBER(value)
The “value” argument must be replaced with the cell reference holding the numerical value.
Here is an example of the ISNUMBER function,
The ISNUMBER function successfully returns a “TRUE” representing a valid date, as the first two dates are valid. On the other hand, the dates in the third cell return a FALSE, representing an invalid date.
Applications of the DATE Function in Excel
Now that we understand the basics of the DATE function let’s look at a few of the practical applications:
- Project Management: Track start and end dates of tasks, calculate durations, and set deadlines.
- Financial Analysis: Analyze trends over time, calculate maturity dates of investments, and forecast future values.
- Scheduling: Manage work schedules, plan events, and coordinate meetings.
Applications are limitless; you can combine the DATE function with others in Excel to create complex formulas for advanced data analysis.
To Summarize: How To Use The DATE Function In Excel
The DATE function is used to simplify working with dates. It helps you combine year, month, and day components into a single cell.
Whether you’re a beginner or an advanced Excel user, mastering the DATE function will enhance your ability to handle date-related data. I hope this article taught you all the bells and whistles of the DATE function in Excel.
Feel free to comment below if you are stuck or have any particular issue using the DATE function. I will try to answer all of your questions as soon as possible.
Also, explore our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.