PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

EXCEL FUNCTIONS-DATE

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 FORMAT

MONTH PUT THE MONTH IN MM FORMAT

DATE 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.

CONTENTS