DATE Function in Excel – Get Date Using Excel Formula

In this tutorial, we would learn about the DATE function in excel. We would cover the purpose, syntax & arguments, and examples of the DATE formula in excel.

We will also unlock, how does the DATE formula behave when any unexpected values are entered. For example, entering day value more than 31, or month value more than 12, etc.

Purpose of DATE Function in Excel

The Excel =DATE formula is used to get the date by entering the year, month, and day value as its input arguments.

Syntax and Arguments of DATE Function

=DATE(Year, Month, Day)

There are three arguments of DATE Function-

• Year – In this argument, enter the year value. For example, ‘2020’ representing the calendar year 2020.
• Month – In this argument, enter the month value. For example, ‘3’ or ’12’ representing the month number. It ranges from 1 to 12.
• Day – In this argument, enter the day value. For example, ’15’ or ’28’ representing the dates. It ranges from 1 to 31 depending upon the month.

Simple Example to Understand DATE Formula

Suppose you want to output a date as ’15-03-2021′, the DATE function in excel can be very useful.

Simply use the following formula in the excel cell.

=DATE(2021,3,15)

As a result, excel would give the cell output as 15-03-2021 or 15/03/2021 or any such other format.

Note that the way a date output is displayed in the cell is controlled by the date and custom formatting of the cell. Check the tutorial on ‘All About Excel Date Format‘ to learn more about it.

Do Not Miss Below Points

• The result of the DATE formula in a cell depends on the cell formatting. For example, a ‘General’ formatted cell would return a number serial of the date in the cell, instead of an actual date.

• It is generally recommended to use a four-digit year value as the ‘Year’ argument. This enables excel to understand the correct year and display the correct date. For example, avoid entering the year argument as ’21’. Instead, mention the full year i.e. 2021.

• While using the DATE formula in excel cell, the month argument is always expected to range any value between 1 and 12. If it exceeds 12, then excel adds the excess over 12 to the next year. For example, =DATE(2020,14,15) will result in 15-02-2021 (i.e. excess 2 months over and above 12 added to the next year).

• Likewise, the month value below 1 would take the date to the previous month.

• In a similar way, the day argument in the DATE formula is always expected to range any value between 1 and 31. Any day value over and above 31 adds up to the next month. For example, =DATE(2020,12,41) will result in 10-01-2021 (i.e. excess 10 days over and above 31 days added to the next month).

Thank You 🙂