The DATE function in Excel is employed to execute the date and time methods and extract the necessary dates and timings. In simple terms, it is all about declaring and managing the calendar dates in Excel. A typical DATE function includes a function call and the argument list. The argument list consists of the date and time parameters. This article on the DATE function covers the following topics.
- What is the DATE function in Excel?
- Syntax of DATE function in Excel
- Examples of a DATE function in Excel
What is the Date Function in Excel?
The DATE function in Excel was introduced in the Excel 2010 version. The prior versions of Microsoft Excel did include date values, but older versions of Excel treated the date values as plain text. In simple words, there was no actual meaning to the data values.
The Excel DATE function takes the calendar and time data or user-defined date information that comprises the time, day, month, and year of the calendar and generates a valid date according to the requirements provided by the user.
The DATE function is proven to minimize the efforts to update the calendar manually. Addition of the DATE function to Excel has improved its capability to update the calendar by itself, dynamically. In the following sections, we will be learning more about the DATE Function syntax in Excel and how to implement it in real-time.
Syntax of DATE Function in Excel
The basic syntax of a DATE function in Excel will include the following parameters.
- Return Date Value
- DATE Function
- Argument list
Return Date Value
The Return DATEVALUE function while implementing the DATE function makes sure that a valid date is being returned as a result of the DATE function.
The actual function starts here. The general syntax for the DATE function is stated as follows;
=DATE(Year, Month, Day)
The default arrangement for the DATE function is the" year, month, and day". The same pattern is expected to follow to minimize the complexity and ambiguity in the data.
As mentioned above, the argument list consists of three critical parameters.
- YEAR - Value of the year
- MONTH - Value of the month
- DAY - Value of the Day / Date
Let us now see various examples of DATE Function in Excel.
Examples of a DATE function in Excel
Following are some of the commonly employed use cases of the DATE function in Excel .
- DATEDIF Function in Excel
- DATE Function in Excel
- YEARFRAC Function in Excel
- EDATE Function in Excel
DATEDIF Function in Excel
The DATEIF function in Excel can be used for multiple purposes. It can be used to find the difference between two specific days, generate a valid date after a particular period, calculate anniversary dates and much more.
Let's try to implement DATEIF function in Excel through a practical example.
The following example is implemented on a small database containing employee details. The DATEIF function is called and employed to calculate the current age of all the employees based on their date of birth.
The following syntax is implemented in the formula bar of the Excel Sheet. The next image shows the real-time results.
The final data table is displayed as shown below.
Next, we will try to execute another example based on the Date Function in Excel.
DATE Function in Excel
The DATE Function in Excel is a simple function to explore multiple rows of data and club them to form a valid date.
Let us imagine we have an employee database. The DOB column only includes the year of birth of all the employees. Now, you are provided with their respective day and month of birth. You can use the two new columns and generate the perfect DOB column.
The syntax for DATE Function in Excel is as follows:
The final resultant data is shown as follows.
In the next section, we will learn about the YEARFRAC Function in Excel.
YEARFRAC Function in Excel
The YEARFRAC Function in Excel is self-explanatory. The primary usage of this function is to find out the difference between any two specific dates. The difference between the two dates is evaluated in terms of years.
Let us understand this through a practical example. In the same employee database used in the previous example, we will include employee joining dates and last working dates. Using the YEARFRAC Function, we will determine the number of years an employee worked in the organization.
We will use the following formula in the Excel formula bar as shown below.
The final data table will be as shown below.
Followed by the YEARFRAC Function in Excel, let us discuss the EDATE Function in Excel.
EDATE Function in Excel
EDATE Function in Excel is a method used to traverse through the calendar and obtain a specific date. For example, let us consider the employee database. Here we have the date of birth, joining date, and age of employees. Using the available data, we will find the employee silver jubilee date (25 years of service).
When you create a new column for Silver Jubilee Date, make sure you change the format of the column to Date Format, as shown below. Unless this change is implemented, we cannot obtain the results as the default mode or format will be general.
We can implement the following formula in the formula bar, as shown below.
The final data table will be displayed as shown below:.
With that, we have arrived at the end of this tutorial on the DATE Function in Excel.
Every aspiring Data Analyst or Business Analyst is expected to have expertise in Excel MIS report which is an interactive Dashboard report. One should be well versed in MIS reports as they are abundantly used in real-time business analytics.
Interested to learn more about Business Analytics? Or wish to get trained and certified to become a successful Business Analyst? Enroll in the Business Analytics certification course from Simplilearn. Ranked among the top 5 business analytics courses by entrepreneur, this program offered by Simplilearn in partnership with Purdue University is an outcome-driven training and certification program that helps you master the fundamental concepts of statistics and data analytics.
If you have any doubts or query on this tutorial on "DATE Function in Excel"or want to know more about our certification course, do reach out to us by sharing them as comments below. Our team of experts will address them and will be happy to answer them at the earliest.