This guide covers everything you need to know about the Google Sheets DATE function, including its definition, syntax, use cases, and how to use it.
What is the DATE Function? How Does It Work?
The DATE function in Google Sheets returns a date value based on the inputs of a specific year, month, and day. The primary purpose of the DATE function is to convert numeric inputs (year, month, and day) into a date format.
This conversion process allows users to maintain consistent date formats across their sheets and enables easier data manipulation. For instance, instead of having separate columns for year, month, and day, users can combine these into a single date cell using the DATE function.
The DATE function in Google Sheets requires numbers as inputs. If you use text or a cell with text, you’ll get a #VALUE! error. Also, if you enter a month or day number that’s too high or low, the function will fix it for you to give a valid date. For instance, if you put in 13 for the month, the function will adjust it to make sure the date still makes sense.
An important thing to know about the DATE function is how it deals with decimal numbers. If you put in a decimal, the function will round it down to the nearest whole number. So, if you enter 12.75 for the month, the function will use just 12 to ensure the date is correct.
Google Sheets uses a date system that starts from January 1, 1900. If you enter a year between 0 and 1899, the program will add 1900 to it to figure out the actual year. For years between 1900 and 9999, it uses your input as-is. But be careful: if you input a year below 0 or above 10,000, you’ll get a #NUM! error.
DATE Syntax
The syntax and arguments for the function are as follows:
DATE(year, month, day)
Here, ‘year’, ‘month’, and ‘day’ represent the components of the date you want to create.
- ‘year’ is the year component of the date.
- ‘month’ is the month component of the date.
- ‘day’ is the day component of the date.
Examples of How to Use the DATE Function
Here are some practical examples of how to use the DATE function in Google Sheets:
Example #1: Basic Usage of DATE Function
The most basic use of the DATE function is to create a date. For instance, if you want to create a date for January 1, 2023, you would use the formula =DATE(2022, 1, 1). This formula will return the date 1/1/2023.
Example #2: Calculating Age
You can also use the DATE function to calculate someone’s age. If you know the person’s birth date, you can subtract it from the current date to get their age. For example, if someone was born on March 20, 1990, you would use the formula =YEAR(TODAY())-YEAR(DATE(1990, 3, 20)). This formula will return the person’s age in years.
Example #3: Calculating Days Until a Future Date
The DATE function can calculate the number of days until a future date. For example, if you want to know how many days are left until December 31, 2023, use the formula =DATE(2023, 12, 31)-TODAY(). This formula will return the number of days until December 31, 2023.
Example #4: Creating a Date from Text Strings
If a date is represented as text strings, you can use the DATE function to convert it into a date. For instance, if you have the year, month, and day stored in cells A1, B1, and C1 respectively, you can create a date with the formula =DATE(A1, B1, C1). This formula will return a date based on the values in cells A1, B1, and C1.
Example #5: Determining the End of a Month
You can use the DATE function with the EOMONTH function to determine the last day of a month. For example, to find out the last day of February 2022, you would use the formula =EOMONTH(DATE(2022, 2, 1), 0). This formula will return the date 2/28/2022, the last day of February 2022.
Why Is DATE Not Working? Troubleshooting Common Errors
If you’re using the DATE function in Google Sheets and it’s not working as expected, there could be several reasons causing this. Here are a few common errors and how to fix them.
#VALUE! Error
Cause: The #VALUE! error typically occurs when the arguments passed to the DATE function are non-numeric or if the date is not valid.
Solution: Make sure all the arguments you pass to the DATE function are numeric. Also, ensure that the date you’re trying to create is valid. For example, a date such as February 30th does not exist and will return a #VALUE! error.
#NUM! Error
Cause: This error is triggered when the parameters provided in the DATE function result in an invalid date. For instance, if you input a month greater than 12 or less than 1 or a day that is greater than the maximum number of days in the specified month.
Solution: Always check the validity of the date you’re inputting. Ensure the month is between 1 and 12, and the day does not exceed the maximum number of days in the specified month.
#REF! Error
Cause: The #REF! error usually occurs when the cell reference provided in the DATE function is invalid. This could happen if you deleted a row or column that was being referenced in the function.
Solution: To fix this, correct the cell reference in your DATE function. Ensure the row or column you’re referencing hasn’t been deleted.
#N/A Error
Cause: The #N/A error typically occurs when the DATE function is nested within a function like VLOOKUP and the lookup value is not found.
Solution: To resolve this, ensure that the lookup value exists in the range you’re searching in. If you’re using the DATE function as a lookup value, check its format and ensure it matches the format of the values in the lookup range.
Using DATE With Other Google Sheets Functions
Combining the DATE function with other Google Sheets functions can allow for even more powerful and flexible data manipulation. The DATE function can be paired with many other functions to create complex formulas that can perform various tasks, from calculating the difference between two dates to determining the day of the week a particular date falls on.
With DAYS
Usage: The DAYS function calculates the number of days between two dates. When combined with the DATE function, you can easily calculate the number of days between a specific date and today’s date.
Example: In Google Sheets, use the following formula to calculate the number of days since the start of the year: =DAYS(TODAY(), DATE(YEAR(TODAY()), 1, 1)).
This formula uses the TODAY function to get the current date, the YEAR function to get the current year, and the DATE function to create a date representing the first day of the current year.
With WEEKDAY
Usage: The WEEKDAY function returns a number representing the day of the week for a specific date, with 1 representing Sunday and 7 representing Saturday. Combining this with the DATE function allows you to determine the day of the week for any given date.
Example: To find out what day of the week January 1, 2022 fell on, you could use the following formula: =WEEKDAY(DATE(2022, 1, 1)).
This formula uses the DATE function to create a date representing January 1, 2022, and the WEEKDAY function to determine the day of the week for that date.
With TEXT
Usage: The TEXT function can convert a number or date into text in a specific format. When used with the DATE function, it allows you to format dates in various ways.
Example: If you wanted to display the date January 1, 2022, as “January 2022”, you could use the following formula: =TEXT(DATE(2022, 1, 1), “MMMM YYYY”).
This formula uses the DATE function to create a date representing January 1, 2022, and the TEXT function to format that date as “January 2022”.
With EDATE
Usage: The EDATE function returns the date that is the indicated number of months before or after a specified date. When combined with the DATE function, you can calculate future or past dates based on a given date.
Example: To find out the date six months after January 1, 2022, you could use the formula =EDATE(DATE(2022, 1, 1), 6).
This formula uses the DATE function to create a date representing January 1, 2022, and the EDATE function calculates the date six months after that date.
For more details on the DATE function, check out the official documentation at the Google Docs Editors Help Center.
