INTRODUCTION
DATE function comes under the DATE AND TIME FUNCTIONS category in Excel.
DATE FUNCTION simply returns the date as a sequential number of the day on that particular date counted form the first day on JAN 1 1900.
THIS PARTICULAR FUNCTION IS USED IN MAXIMUM FINANCIAL FUNCTIONS TO ENTER THE DATE.
PURPOSE OF DATE FUNCTION IN EXCEL
DATE FUNCTION returns a sequential number which represents a particular date counted from JAN 1 1900.
PREREQUISITES TO LEARN DATE
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the financial terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: DATE FUNCTION
The Syntax for the function is
=DATE(YEAR, MONTH, DATE)YEAR PUT THE YEAR IN YYYY FORMATMONTH PUT THE MONTH IN MM FORMATDATE PUT THE DATE IN DD FORMAT
THE DATES SHOULD BE PUT USING THE DATE FUNCTION
=DATE(YYYY,MM,DD) OTHERWISE IT’LL RETURN AN ERROR. *IF THE FORMAT OR NUMBER TYPE OF THE CELL IS NOT DATE, IT’LL SHOW YOU A NUMBER WHICH WILL BE A SEQUENTIAL DATE. JUST CHANGE THE FORMAT OF THE CELL AND YOU’LL GET THE DATE IN THE FORMAT AS YOU WANT.
EXAMPLE:DATE IN EXCEL
DATA SAMPLE
We can make use of DATE FUNCTION to enter the date. CASE 1: Let us enter FEB 9 2020 and check what number it gives as output when CELL IS NOT SET THE NUMBER TYPE FOR DATE.
CASE 2: When cell format is DATE.
STEPS TO USE DATE FUNCTION
The example enters the date in excel using the DATE FUNCTION.
CASE 1:function used :=DATE(2020,2,9)
The format of the cell is not DATE and we can see the DATE is shown as a number.
CASE 2:function used:=DATE(2020,2,9)
The output format is DATE and date is shown as a date.
The format of the DATE can be changed as per requirement. When DATE function is used, a GENERAL FORMAT CELL is itself converted into a DATE FORMAT.
CONFUSION CLARIFICATIONS
DATE FUNCTION OPTIONS
DATE function works in a very flexible manner. It acts as a sequential number and we have many wrong formats which will result in some solution
such as
=DATE(2020,02,30)
This date won’t generate any error for there are no 30 days in Feb but it’ll show this date as 1st MARCH 2020.
Similarly the following translations are there
=DATE(2020,13,01)
will be treated as
1st JAN 2021 as there are no 13 months in an year but it’ll create one year of the twelve months and start the next month and year.
