The DATE function in Google Sheets takes year, month, and day as input to create a valid date, which is acceptable in Microsoft Excel as well. Select the “Insert” tab from the main menu and click the “Function” option to access this formula.
Dates can be written in various formats. However, not all these formats are accepted as valid dates in Google Sheets.
An invalid date can cause other functions not to work.
In this article, let us discuss using the DATE function in Google Sheets. Read until the end because we will uncover the DATE function syntax, examples, and errors.
Also, it includes changing the date format to your preference.
Explained: The DATE function in Google Sheets
The DATE function is a simple formula in Google Sheets. It ensures that the dataset contains valid dates acceptable to spreadsheet software.
The general syntax is as follows,
=DATE(year, month, day)All of the three arguments are self-explanatory. But let’s quickly look at them,
- “year” – This argument must be replaced with the year for your date. It accepts a four-digit number
- “month” – Here, you need to put the two-digit number that represents the month for your date
- “day” – Similar to the second argument, the “day” accepts a two-digit number that represents the day for your date
You can either manually enter the values or select the cell reference containing the same for all three arguments of the DATE function.
Note that all the decimal values (both higher and lower) will be rounded to the nearest integer number. For example, the function will consider the “10.43” as “10” while generating the month.
Download/Copy the Example Google Sheet
You can download/copy the following Google Sheet that contains the spreadsheet with datasets we will use to demonstrate the DATE function in this article.
By practicing the things we are about to discuss in the following section, you will surely become an expert at organizing datasets by date.
Having your own Google Sheet ready with the necessary data (dates) is fine. You can continue with that.
How to use the DATE function in Google Sheet
Consider the following example where we have club member names in one column and their birth year, month, and date in three separate columns.
- The year is in column B
- The month is in Column C
- The Day is in the Column D
Let us use the DATE function to combine birth year, month, and date to get the birthdate of each club member.
Here are the steps to use the DATEVALUE function,
- Open the Google Sheet
- Select the desired cell. In our case, we will click on the first cell below the column header “Date”
- Type “=date”
- Click on the first option from the popup or press “Tab” on your keyboard
- For the “year” argument, we will provide the cell reference as “B2”
- Press “,” key to move to the next argument
- For the “month” argument, we will put the cell reference as “C2”
- Press “,” key
- For the “day” argument, we will provide the cell reference as “D2”
- Close the parenthesis using “)” on your keyboard
- Press “Enter” key
Here’s how the final formula should be,
=DATE(B2,C2,D2)The Google Sheet will instantly create a date using the values from the cells B2 (year), C2 (month), and D2 (day).
The date is represented in the United States format as month, day, and year (mm/dd/yyyy).
Now, let us apply this formula to the rest of the cells. Go to the cell (E2) where we have used the formula by following the above steps. Hover to the lower-rightmost corner of the cell until you see the “+” icon. Now, click on that icon and drag the formula for the rest of the cells.
I am experiencing #VALUE! Error
This is because the DATE function in Google Sheets works only with integer values. The function is not compatible with text strings.
Consider the following example where we have put a text string as “September” in the Month column.
After using the DATE function, it returns an error as “Function DATE parameter 2 expects number values. But ‘September’ is a text and cannot be coerced to a number.”
I am experiencing #NUM! Error
It is due to an invalid year entry. The DATE function accepts only four digits in the place of the “year” argument.
Consider the following example,
Here, we have put the number “1” at the end of “1992” to make it look like “19921”. As it becomes a five-digit number, it won’t be accepted by the DATE function. You will see an error that says, “Function DATE parameter 1 value 19921 is out of range.”
Q. What if I put a number for the Month argument beyond 12?
If the value for a month is beyond the number 12, the function will add the additional months beyond 12 to the date.
Consider the following example where we replace the month number from “09” to “15”.
Here, the desired output is 9/28/1992. However, as we changed the month number to 15, the outcome is 3/28/1993.
For days, it works similarly. If you put anything beyond 31 or 28 for February and 30 for the specific months, the function will add these additional days and return the results.
Q. What is another method to access the DATE function in Google Sheets?
You can access the DATE function in Google Sheets in two ways.
In the first method, you simply select the desired cell and start typing “=date”, then select the first option from the popup.
Now, if you don’t prefer manually typing the formulas in Google Sheets, then here is the second method to access the DATE function in Google Sheets.
- Open the Google Sheet
- Click on the desired cell
- Go to the main menu and click on the “Insert” tab
- Select “Function” from the popup
- Then, you need to click on “Date”
- You will see a list of Google Sheets functions to manage and organize datasets by dates
- Choose the first option, “Date”
It is a lengthy process to access the DATEVALUE function. We suggest selecting the cell and typing the formula instead of manually locating it through the main menu.
How to change the Date format in Google Sheets
As mentioned, the date format obtained using the DATE function is mm/dd/yyyy.
If you wish to change the date format to be more visually appealing, follow the steps below.
- Go to the main menu
- Make sure to select all the cells containing dates
- Click on the “Format” tab
- Choose the “Number” option from the popup
- Select “Custom date and time”
- A new dialog box will appear
- Select the date format from the list as per your preference
- Click on the “Apply” button in the green
You have a wide range of predefined date formats to choose from in Google Sheets.
Additionally, you can create your unique date format using the following section of the dialog box that appears in Step 5 above.
You can represent a day with or without leading zero as an abbreviation and full name of the day. Refer to the following image.
Similarly, a month can be represented in various formats as below.
Apart from the above, the year can be represented as a two-digit or full numeric year.
How to change the default date format from the United States to any other country
If you are not from the United States and your country follows a specific date format. For example, in the United Kingdom, the date starts with the day, followed by the month and year, compared to the United States, where the date starts with the month.
Let us learn to change the Google Sheet location to the United Kingdom.
- Hover to the main menu and click on “File” option
- Next, select “Settings”
- A new dialog box will open
- Click on the dropdown below “Locale” to see the list of available countries
- Choose “United Kingdom” from the list
- Click on the “Save and reload” button in the green
You can change the default date format to any country. Make sure to explore the list that appears in step 4, as discussed above, to find out if your country is listed there or not.
Once you change the default date format, the date will automatically be formatted to the selected country the next time you use the DATE function.
How to check a valid date in Google Sheets
There are several methods to identify if the date in the cell is in the correct format and acceptable by Google Sheets.
A valid date format is crucial as it lets you manage the datasets by date through filtering, sorting, and performing various calculations.
Method #1 – Check the alignment of the Date
Valid dates are aligned to the right side of the cell, as shown in the following image.
Here, the dates in the first three cells are valid, whereas those in the fourth and fifth rows are invalid.
If Google Sheets does not accept a date format, it is shifted to the left side of the cell and treated as a normal text string.
Method #2 – Use the ISDATE function
The ISDATE function returns TRUE for valid dates and FALSE for invalid dates.
It is a simple function by Google Sheets to validate the dates. The general syntax for this formula is as below,
=ISDATE(value)Where the “value” argument needs to be replaced with the date you wish to check. You can manually type the date or provide the cell reference value containing the same.
Also, use double quotation marks if you plan to type the date manually.
Here is an example of the ISDATE function in Google Sheets.
For the first three dates, the ISDATE function returned the value as TRUE, which means they are valid and accepted by Google Sheets.
Method #3 – Use the DATEVALUE function
The DATEVALUE function returns a serial number that represents the date in Excel.
You can use it to check whether the date is valid, as it returns the serial numbers for valid dates and errors for invalid dates.
The general syntax for this formula is as follows,
=DATEVALUE(date_string)The “date_string” argument must be replaced with the date you wish to check.
Here is an example of the DATEVALUE function,
As the first three dates are valid, the DATEVALUE function successfully returned serial numbers for them.
On the other hand, the dates in cells B5 and B6 are invalid, so the function has returned the errors as shown below.
Final Words
Dates are crucial for data analysis.
They may represent anything from task deadlines, hire dates, event schedules, and much more.
Google Sheets boasts a range of powerful functions to organize and analyze datasets by date. You can easily filter, sort, highlight, and perform various calculations by date to prepare comprehensive reports.
Don’t forget to check out our blog section. It contains useful articles about various date formulas available in Google Sheets.
I hope this article taught you all the bells and whistles when it comes to using the DATE function in Google Sheets.
Feel free to comment below if you are stuck somewhere while using the DATE function or have any specific errors. I will try to answer all of your questions as soon as possible.