In this article, I’m going to show you exactly how to use the DATE function in Google Sheets. This function returns a date in a standardized format. It can be useful when working with large amounts of data or organizing regional date formatting.
Syntax of the DATE function in Google Sheets
The syntax of the DATE function is as follows:
=DATE(Year, Month, Day)
Let’s break down the syntax:
=DATE: Used to convert the following inputs into a date format in Google Sheets.Year: This value will be displayed as the year in the results and can be a cell reference or a specific number. It must always be the first in the syntax order.Month: This value will be displayed as the month in the results and can be a cell reference or a specific number. It must always be the second in the syntax order.Day: This value will be displayed as the day in the results and can be a cell reference or a specific number. It must always be the last in the syntax order.
It’s important to note that while the format in the syntax should be year, month, and day (and will be displayed in this order by default), the format of the results can be modified in the formatting options:
Format > Number > More Formats > More Date and Time Formats.
Examples of the DATE function in Google Sheets
For these demonstrations, we have created a set of sample data. Click here to access the link and follow along with the examples.
Example 1: Using the DATE function with cell references in Google Sheets
- Select an empty cell next to the data.
Next to your data, select an empty cell. In our example, we have created an additional header for the data and selected a cell next to the data.
- Enter the DATE formula in the cell.
In the empty cell, enter the formula:
=DATE(Year, Month, Day)
Let’s break down the formula:
=DATE: Used to convert the following inputs into a date format in Google Sheets.Year: This can be a cell reference or a numerical value representing the year. In our example, it is cell C4 containing the value 2023.Month: This can be a cell reference or a numerical value representing the month. In our example, it is cell D4 containing the value 1.Day: This can be a cell reference or a numerical value representing the day. In our example, it is cell E4 containing the value 23.
- Press Enter to see the results.
Press Enter, and the complete results will be displayed in the cell.
Example 2: Using the DATE function to display real-time values in Google Sheets
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.
Select an empty cell next to the data. In our example, we have created an additional header for the data and selected a cell next to the data.
- Enter the DATE formula in the cell.
In the empty cell, enter the following formula:
=DATE(Year, Month, (Day(TODAY())))
Let’s break down the formula:
=DATE: Calls the DATE function in Google Sheets.Year: This can be a cell reference or a numerical value representing the year. In our example, it is cell D4 containing the value 2023.Month: This can be a cell reference or a numerical value representing the month. In our example, it is cell E4 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 DAY value, which means Google Sheets will return the current day value. This can be used for MONTH and YEAR as well.
Please note: TODAY() will always update to display the actual real-world value every time the spreadsheet is reopened.
The complete example is:
=DATE(D4, E4, (DAY(TODAY())))
- Press Enter to see the results.
Press Enter, and the results will be displayed in the cell.
Example 3: Using obscure numerical values with the DATE function in Google Sheets
In this example, we will show what happens when you enter a numerical value that doesn’t fit the usual DATE format.
- Select an empty cell next to the data.
Select an empty cell next to the data. In our example, we have created an additional header for the data and selected a cell next to the data.
- Enter the formula
=DATE(Year, Month, Day)in the cell.
In the empty cell, enter the following formula:
=DATE(Year, Month, Day)
This time, instead of entering cell references, we will enter numerical values directly. In our example, it looks like this:
=DATE(2022, 12, 32)
However, please note that the value of the Day in this example is set to 32. Since there are only 31 days in the 12th month (December), Google Sheets adds an additional 1 day to the total date.
In this example, we can see that the preview displays 01/01/2023 due to this extra day.
Please note that this will also happen if the numerical value of the Month parameter follows this behavior when the number is greater than 12 (or less than 1).
- Press Enter to see the results.
Press Enter to apply, and the results will be displayed. In our example, the final result is 01/01/2023.
We hope this article has helped you gain a better understanding of how to use the DATE function in Google Sheets. You might also like our articles on the TODAY function in Google Sheets and how to subtract dates in Google Sheets.
By the way, make sure to check out more articles like this on Crawlan.com.
If you have recurring deadlines, try our recurring deadlines tracking software!
