The DATE function converts a year, month, and day into a date.
Syntax=DATE(year, month, day)
Sample Usage=DATE(2020, 3, 6)
//This returns the date in the format last used–eg, 6/3/2020=DATE(A2, B2, C2)
Where A2, B2, C2 contain the year, month, and day respectively.
//This will return the date in the formula cell.
We will have a look at the DATE function in Google Sheets and see how to reference the month, day and year from another cell. We will also see how to change the date format in Google Sheets.
Convert Year, Month and Day into a date using Date function in Google Sheets
We can use the DATE function to achieve the given problem. The syntax for the same would be
DATE(year, month, day)
Here year, month and day are the respective components of the given date. An example for the same would be something like this
DATE(2020, 3, 6)
Some things to keep in mind whilst using the formula are as follows:
- Inputs to the functions must be proper strings, numbers or references for another cell
- DATE will truncate the decimals number that you enter. For example, 15.45 will be treated as 15.
- Google sheets use a 1900 date system, i.e the first date is 1/1/1900. If you use any data which has a year from 0 to 1899, that year will be added to 1900. For example DATE(203, 1, 1) will be calculated as DATE(2103, 1, 1).
- For dates with year less than 0 or more than 10,000 will return an error( #NUM!).
Also, keep in mind that the resultant value is in the format of MM/DD/YYYY.
Change the date to the European format
It can happen that you don’t want the American format for the date (MM/DD/YYYY). Many countries are used to the European format (DD/MM/YYYY). To change the format you simply have to change the location settings for the spreadsheets. To achieve the same do the following steps.
- Go to File->Settings
- Set the desired country and the desired time zone.
- You’ll see that the dates have been set to the desired format.
Conclusion
In this article, We saw how to reference and create a date in Google Sheets. This was achieved using the DATE function in Google Sheets. You also learn how to change the date settings to follow the European format. You can also check out the Numbers of Days, Months and Years between two dates. You can check the official documentation for DATE function in Google Sheets here.
See Also
Loved this article on the DATE function in Google Sheets? Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Tips & Tricks: Working with Date and Time in Google Sheets: We will look at some commonly used tips, tricks and functions related to date and time in Google Sheets.
Google Sheets: Numbers of Year, Months and Days between two dates: We will look at a function DATEDIF in Google Sheets and will look at how to find the number of Years, Months and Days between two dates.