Date Function

So far I have discussed about various functions in Excel regarding Logical, Text, Mathematical etc., but Date and Time has its' own importance in Data Analysis in Microsoft Excel.

Date, one of the most important element in Management Data, to make a decision.

In today's article and in upcoming articles we will discuss about the Date & Time functions available in Excel.

The first function we'll discuss today is DATE(). Very simple function and very Easy to implement.

Description

The Microsoft Excel DATE function returns the serial date value for a date. Here Serial Date Value means the number that Excel uses in calculating dates and times entered into a worksheet, either manually or as a result of formulas involving date calculations.

If your cell is formatted as General and you enter the DATE function, Excel will format your result as MM/DD/YYYY  based on your Regional Settings. If you wish to see the serial number result from the DATE function, you will have to change the format of the cell to General after entering the formula. (We'll discuss about Regional Settings in next article.)

Syntax

The syntax for the DATE function in Microsoft Excel is very simple, you need to enter three parameters as mentioned below.

=DATE( Year, Month, Day )

Parameters or Arguments

Year

A number that is between one and four digits that represents the year.

Month

A number representing the month value.

Important Note : If the month value is greater than 12, then every 12 months will add 1 year to the year value. This means that if the date is entered as DATE(2017,13,12) is equal to DATE(2018,01,12).

For Example, if Month is entered as 13 to 24, the year will increase by 1, if you put the month value as 24, year increases to 2 more, 36 will increase the year to  years more.

Day

A number representing the day value. If the day value is greater than the number of days in the month specified, then the appropriate number of months will be added to the month value.

It is also works in the same manner as we've discussed for case of Month, if day is increased by 1, the month will be the next month and so on.

Let us take some Examples to understand it better.

In the below example I've entered Year, Month & Day in three consecutive columns and applied the DATE function in the next Column named as Invoice Date.

Date Function in Excel

The Date function is very easy to use, you need to enter year, month and day separated by comma and it will give you the date in date format which is set in your Regional Settings (I will show how to adjust regional settings in my next article).

If you carefully notice the table given you can easily find out that I have entered day as 32 in 3rd row and month as 13 in the 4th row.

Now the question is what DATE function will do in this cases ?

Simple, the DATE function will add one more month to the existing month i.e., 12+1=13, but 13 month is not possible, therefore, Excel will add one year to the existing year i.e., year 2001+1=2002.

The date now becomes 01-01-02 which was 32-12-01, which is not possible.

The same is for the next row. The year is 2005 but as I have entered the month as 13 (which is not possible) DATE function add one year to the 2005 and it becomes now 2006.

Serial Date Value : It is the numerical representation of Date & Time which Excel uses to calculate Date.

To see the Serial Date Value, you need to change the Date format to General format in the Number Format box under Home menu Number Tab, as shown below.

Serial Date Value in Excel

Hope you have enjoyed this article. In Microsoft Excel there are few more functions related to Date & Time which I'll discuss in my upcoming articles.

Keep reading and keep growing your knowledge in Excel...

Thank you for reading...