DATEVALUE

• How to use DATEVALUE formula?
• Examples of using DATEVALUE formula
• DATEVALUE formula not working?
• Similar formulas to DATEVALUE

Usage

Use the DATEVALUE formula with the syntax shown below, it has 1 required parameter:

=DATEVALUE(date_string)

1. date_string (required):
   The date string that you want to convert into a date value. This can be in a variety of formats, such as "12/31/2021" or "31-Dec-2021". Note that the format of the date string must be recognized by Google Sheets in order for the formula to work correctly.

Examples

Converting imported data into dates

When data is imported into Google Sheets from other sources, such as CSV files, date values may not be recognized as such by Google Sheets. The DATEVALUE formula can be used to convert these date strings into a format that Google Sheets can work with.

Calculating differences between dates

In order to calculate the difference between two dates in Google Sheets, the dates must be in a recognizable date format. The DATEVALUE formula can be used to convert date strings into date values that can be used in date calculations.

Filtering data by date

When working with large datasets that include date values, the DATEVALUE formula can be used to convert date strings into date values that can be filtered by date range.

Common Mistakes

Incorrectly formatted date_string

The DATEVALUE formula only accepts date_string in the format of 'YYYY-MM-DD' or 'MM/DD/YYYY'. If the date_string is in a different format, it will return an error. To correct this mistake, ensure that the date_string is in one of the accepted formats.

Non-existent date

If the date_string provided does not represent a valid date, the DATEVALUE formula will return an error. To correct this mistake, ensure that the date_string represents a valid date.

Missing or incorrect argument

The DATEVALUE formula requires one argument, the date_string, to function properly. If this argument is missing or incorrect, the formula will return an error. To correct this mistake, ensure that the date_string is provided as the formula's argument.

Related Formulas

The following functions are similar to DATEVALUE or are often used with it in a formula:

• DATE

  The DATE formula is used to create a date object in Google Sheets. It takes three arguments: year, month, and day and returns a date. This formula is useful when you need to perform date calculations or create date-based charts and graphs.

• DATEDIF

  The DATEDIF function in Google Sheets calculates the difference between two dates using a specified unit of measurement. This function is commonly used to calculate the length of time between two dates, such as the number of days, months, or years. The function is particularly useful when working with financial or project planning data.

• YEAR

  The YEAR formula in Google Sheets returns the year of a specified date. This function is useful when you need to extract the year from a date for analysis or calculations. The YEAR formula takes one argument, which is the date from which to extract the year. The output is a four-digit number representing the year.

• MONTH

  The MONTH formula is used to extract the month from a given date, returning it as a number between 1 and 12. This function is commonly used to summarize and analyze data that includes dates.

• DAY

  The DAY function is used to extract the day of the month from a given date. It takes in a single argument, a date in a valid format, and returns the day of the month as a number between 1 and 31. This function can be useful for tasks such as sorting or filtering data based on the day of the month.

Learn More

You can learn more about the DATEVALUE Google Sheets function on Google Support.