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

HOW TO USE DATE FUNCTION IN GOOGLE SHEETS

INTRODUCTION

DATE function comes under the DATE FUNCTIONS category in GOOGLE SHEETS.

DATE FUNCTION simply converts a given YEAR, MONTH and DATE into DATE .

THIS PARTICULAR FUNCTION IS USED IN MAXIMUM FINANCIAL FUNCTIONS TO ENTER THE DATE.

PURPOSE OF DATE FUNCTION IN GOOGLE SHEETS

DATE FUNCTION converts the given year, month and date into DATE in Google Sheets.

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 GOOGLE SHEETS.
Some information about the financial terms is an advantage for the use of such formulas.
Of course, access to GOOGLE SHEETS.

WHAT IS THE SYNTAX OF 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 GOOGLE SHEETS

DATA SAMPLE

We can make use of DATE FUNCTION to enter the date.

We’ll take different inputs to convert them into date.

The following examples will make the use of date function more clear.

FIND OUT THE DATES REPRESENTED BY THE GIVEN DATA:

YEAR	MONTH	DATE
2020	11	20
2020	14	21
1899	1	1
2020.2	1.5	2.6
2020	1	1

STEPS TO USE DATE FUNCTION

Select the cell where you want to insert the Date.
Enter the Formula as =DATE(YEAR, MONTH , DATE). The year, month and date can be given directly or as references i.e. cell address of the cells containing the year, month and date.
Click Enter.
The date will appear in the cell.

The examples with the formula used are shown in the picture below. Example wise explanation follows the picture below.

EXPLANATION

All the examples have the same format but the different values.

The function used is DATE( YEAR, MONTH, DAY) in which we pass different values to understand the different scenarios of the DATE FUNCTION. Let us understand the behavior of the function with different set of inputs.

EXAMPLE 1:

We have the simple input of year, month and date as 2020,11 AND 20. The result is a date 11/20/2020 which is correct. The FORMAT OF THE DATE CAN BE CHANGED EASILY WITH THE HELP OF TEXT FUNCTION.

EXAMPLE 2:

The values of year , month and day are 14, 21 and 2020. Now this one is having some problem.

Here are the different problems with this date.

There are no 14 months in an year.

Rest two values are OK.

Refer to the picture above and we can see that DATE FUNCTION DIDN’T RETURN ANY ERROR BUT EVALUATED THE FUNCTION WITH THE SAME DATA.

THE DATE FUNCTION WILL ADD THE ADDITIONAL MONTHS OR DAYS [ OVER THE VALID LIMIT OF MONTHS OR DAYS ] TO THE DATE AND EVALUATE THE RESULT.

In the light of above fact, we can see that the DATE FUNCTION simply added the 2 over the limit months to the date and gave the result which is 2/21/2021.

Same is with the case of DATE which we’ll see in the further examples.

EXAMPLE 3:

The dates in the GOOGLE SHEETS are represented a numeral which is the count of the days from the 1.1.1900.

If any date prior to the standard initial date is given, it gives the result by adding 1900 years to this.

In this example the year, month and day are 1899, 1 and 1. The result is simply by adding 1900 years i.e. 1.1.3799

EXAMPLE 4:

The value of year , month and day are 2020.2 , 1.5 and 2.6

The result in this case is 1/2/2020 which means that date function removed all the decimal part and kept the whole number only and calculated the date.

EXAMPLE 5:

It has the value of year, month and day as 2020, 1 and 1.

It simply shows that we can also enter the date by passing the values directly into the function.

For example, the date has been entered as =DATE (2020,1,1).

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