Table of Contents
- INTRODUCTION
- PURPOSE OF DATE FUNCTION IN GOOGLE SHEETS
- PREREQUISITES TO LEARN DATE
- WHAT IS THE SYNTAX OF DATE FUNCTION ?
- EXAMPLE: DATE IN GOOGLE SHEETS
- CONFUSION CLARIFICATIONS
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, DAY)
YEAR PUT THE YEAR IN YYYY FORMAT
MONTH PUT THE MONTH IN MM FORMAT
DAY PUT THE DAY IN DD FORMAT . Day is the DAY NUMBER of the month.
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 the 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.
The 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 as numerals which is the count of the days from 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 the date function removed all the decimal parts and kept the whole number only and calculated the date.
EXAMPLE 5:
It has the value of the 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 a year but it’ll create one year of the twelve months and start the next month and year.
