In this article we will show you exactly how to use the DATE function in Google Sheets. This returns a date as a standardized output. This can be useful when working with large amounts of data or to organize regional date formatting.
In this article we will show you how to use this useful function.
The syntax for the DATE function is:
=DATE(Year, Month, Day)
Syntax Breakdown:
=DATE: This is used to convert following inputs to the date format in Google Sheets.
Year: This value will display as the year in the results and can be inputted as a cell reference or specific number. This must always be first in the syntax order.
Month: This value will display as the month in the results and can be inputted as a cell reference or specific number. This must always be second in the syntax order.
Day: This value will display as the day in the results and can be inputted as a cell reference or specific number. This must always be last in the syntax order.
It is important to note that although the format in the syntax must be year, month and day (and by default will be displayed in this order) - the format of the results can be changed in the formatting options:
Format > Number > More formats > More date and time formats.
For these demonstrations an example data set has been created. Click here to access the link and follow along.
Next to your data select an empty cell. In our example we have created an additional heading for the data and selected a cell next to the data.
In the empty cell enter the formula:
=DATE(Year, Month, Day)
Formula Breakdown:
=DATE: Used to convert following inputs to the date format in Google Sheets
Year: This is the cell reference or numeric value representing the year. In our example this will be the C4 cell containing the value 2023.
Month: This is the cell reference or numeric value representing the month. In our example this will be the D4 cell containing the value 1.
Day: This is the cell reference or numeric value representing the day. In our example this will be the E4 cell containing the value 23.
Press enter, the completed results will be populated in the cell.
In this example we will show how the DATE function can be used in combination with the TODAY function to return real-time data.
Select an empty cell next to the data. In our example we have created an additional heading for the data and selected a cell next to the data.
In the empty cell enter the formula as:
=DATE(Year, Month, (Day(TODAY())
Formula Breakdown:
=DATE: Calls the DATE function in Google Sheets.
Year: This is the cell reference or numeric value representing the year. In our example this will be the D4 cell containing the value 2023.
Month: This is the cell reference or numeric value representing the month. In our example this will be the E4 cell containing the value 1.
DAY(TODAY()): By entering this, Google Sheets will always use the current real world value. In our example we will specify the value of DAY which means Google Sheets will return the value of the current day, this can be used for MONTH and YEAR.
Please note: using TODAY() will always update to display the real-world value whenever the spreadsheet is re-opened.
The complete example is:
=DATE(D4,E4,(DAY(TODAY())))
Press enter, the results will be populated in the cell.
In this example we will demonstrate what happens when a numerical value is entered which is not part of the usual DATE format.
Select an empty cell next to the data. In our example we have created an additional heading for the data and selected a cell next to the data.
In the empty cell enter the formula as:
=DATE(Year, Month, Day)
This time instead of entering the cell references we will enter the numerical values directly. In our example this is:
=DATE(2022,12,32)
Note however that the Day value in this example is set to 32. As there are only 31 days in the 12th month (December) Google Sheets adds the additional 1 day to the total date.
In this example we can see the preview is showing 01/01/2023 due to the additional day.
Please be aware that this will also occur if the numerical value of the month parameter will follow this behavior if the number is greater than 12 (or less than 1).
Press enter to apply, the results are populated. In our example the returned result is 01/01/2023
We hope this article has helped you and given you a better understanding of how to use the Google Sheets DATE function. You might also like our articles about the Google Sheets TODAY function and how to subtract dates in Google Sheets.
On a side note, we also recommend reading our guide on how to add a reminder in Google Sheets.
