Skip to Content

How to Use the DATEVALUE Function in Google Sheets

The DATEVALUE function in Google Sheets is a built-in function that allows you to convert a date represented as text into a serial number that Google Sheets recognizes as a date.

This function can be useful when you have dates stored as text in your spreadsheet and you want to perform date calculations or sorting based on those dates.

You need to go to the “Insert” tab of the main menu and select “Function” to access the DATEVALUE formula in Google Sheets, or simply type it into the cell beginning with an equal sign.

That being said,

This is a quick guide about the DATEVALUE function in Google Sheets, where we’ll discuss everything from the formula to use cases. Make sure to read this article until the end because it is all you need when it comes to the DATEVALUE function.

Why Use the DATEVALUE function to convert dates to serial numbers?

The dates column may represent anything from task deadlines, hire dates, birthdays, and event schedules.

Often, handling dates in both Google Sheets and Microsoft Excel is a daunting task because dates can be written in various formats.

All those formats may be easy to interpret for us as human beings. However, they may confuse spreadsheet programs like Google Sheets, leading to errors while calculating, filtering, and sorting datasets by dates.

The DATEVALUE function in Google Sheets is used for several purposes, as stated below:

  1. Converting Text Dates to Date Values: It’s commonly used when you have dates stored as text in your spreadsheet and you want to convert them into a format that Google Sheets recognizes as dates. This is important because working with date values allows you to perform various date-related calculations and sorting functions.
  2. Date Calculations: Once you have date values, you can perform calculations with them. For example, you can calculate the number of days between two dates, determine the day of the week for a given date, or add or subtract days from a date. Without converting text dates to date values, such calculations would be difficult or impossible.
  3. Sorting and Filtering: Date values can be sorted chronologically, making it easy to arrange data in date order. This is particularly useful when dealing with datasets that include dates, such as sales records, project deadlines, or event schedules. Sorting by text dates may not produce the correct order.
  4. Charting and Graphing: If you want to create charts or graphs based on date data, using date values rather than text dates is essential. Google Sheets can plot data accurately only if it recognizes the data as dates.
  5. Conditional Formatting: You can use date values with conditional formatting rules to highlight dates that meet specific criteria. For instance, you can highlight dates that are overdue or upcoming.

In summary, the DATEVALUE function is a crucial tool for working with date-related data in Google Sheets. It allows you to convert text dates into a format that can be used for calculations, sorting, charting, and various other tasks involving dates.

DATEVALUE Function Syntax

The DATEVALUE is a simple function in Google Sheets. The general syntax for this formula is as follows,

=DATEVALUE(date_string)

Here, the “date_string” argument must be replaced with the date text string or cell reference containing the dates as a text string.

Copy the Example Google Sheets

You can copy the following Google Sheet, which contains the spreadsheets and the data we will use to practice the DATEVALUE function.

Click Here to Copy!

Follow me along as we move through this article for a better understanding.

If you have your own Google Sheet ready with date data to practice, you can skip downloading the above example Google Sheet.

How to use the DATEVALUE function in Google Sheets

As mentioned earlier, you don’t need to be an expert in Google Sheets to use the DATEVALUE function.

Task: Convert the Hiring Date for employees to the serial numbers. We have a total of 20 employees, as shown in the following image.

Here are steps for using the DATEVALUE function,

  • Type “=DATEVALUE
  • Select the first option from the popup or pressTab” on your keyboard
  • Provide the cell reference as “D2
  • Close the brackets using “)
  • PressEnter” on your keyboard
How to use the DATEVALUE function in Google Sheets

To apply this function to the rest of the cells, use the Auto Fill dialog box as shown in the image above or hover to the lower-rightmost corner of the current cell until you see the “+” icon. Then, click on that “+” icon and drag the formula for the rest of the cell.

Google Sheets will instantly convert all of the dates that are valid to serial numbers.

If any of the hiring dates were in incorrect format, Google Sheet would have returned an error as the “DATEVALUE parameter cannot be parsed to date/time.”

Q. What is another way to access the DATEVALUE function in Google Sheets?

There are two ways to access the DATEVALUE function: manually entering the formula by typing “=datevalue” or selecting it from the main menu.

The first option is pretty straightforward as illustrated above.

However, if you do not want to use the function by manually typing, then follow the steps below.

  • Hover to the main menu and click on the “Insert” tab
  • Choose the “Function” option from the popup
  • Now, select the “Date” option
  • Click on “DATEVALUE

Both are quick; choose one of them based on your preference.

The most common mistake while using the DATEVALUE function in Google Sheets

Dates can be written in a variety of formats. However, not all the dates users provide are interpreted as valid dates in Google Sheets.

Here is a list of acceptable date formats in Google Sheets.

Date FormatDate Format in Google SheetsExample
Month – Date – Yearmm-dd-yyyy08-20-2023
Year – Month – Dateyyyyy-mm-dd2023-08-20
Date – Month- Yeardd-MM-yyyy20-Aug-2023
Month – Date – YearMM-dd-yyyyAug-20-2023
Month / Date / Yearmm/dd/yyyy08/20/2023
Year / Month / Dateyyyyy/mm/dd2023/08/20
Date / Month / Yeardd/MM/yyyy20/Aug/2023
Month / Date / YearMM/dd/yearAug/20/2023
Month, YearMM, yyyyAugust, 2023
Month Date, YearMM dd, yyyyAugust 20, 2023

Now, let us use the DATEVALUE function for the above dates and check if they are valid and converted into serial numbers.

Note that the separators you can use between each element are dash (-), slash (/), and space ( ).

These are the most common date formats you may wish to use most of the time. To explore more date formats, you can follow the steps below:

  • Hover to the main menu and click on “Format” tab
  • Select the second option displayed as “Number” from the popup
  • Next, click on “Custom date and time
  • A new dialog box will open
  • Choose a preferred date format from the list
  • Click the “Apply” button in the green

This is a complete list of date formats supported by Google Sheets. You need to ensure that your provided date format follows one of these predefined formats to be compatible with the DATEVALUE function.

Note that you can also create a custom date format using the following section of the dialog box that opens after selecting the “Custom date and time” option.

Q. What is the quickest way to check if the Date is formatted properly?

All dates that are formatted correctly are aligned to the right of the cell. Refer to the following image, where the first three cells contain valid dates, and the remaining two cells contain invalid dates.

Precaution: While using this method to check the date formatting, you must ensure that the cell’s width is more than the width of the content (date entered) in the cell.

Apart from the above method, you can use the “ISDATE” function in Google Sheets to check if the dates entered in Google Sheets are in acceptable format.

The ISDATE function returns “TRUE” for valid dates and “FALSE” for invalid dates, as shown below.

The general syntax for the ISDATE function is as follows,

=ISDATE(value)

You can manually enter the date to replace the “value” argument or provide the cell reference containing the date value.

Here are the steps to use the ISDATE function,

  • Type “=isdate
  • Select the first option from the popup or pressTab” on your keyboard
  • Provide the cell reference. In our case, we’ll put “B2” as a cell reference
  • Close the bracket by typing “)” on your keyboard
  • Press “Enter

How to Convert Number to Date in Google Sheets

The DATEVALUE function lets you convert a date into a serial number.

But what if you download the data from a source and notice that all the dates have been converted to numbers? And, so, your next job is to convert those numbers to date.

In this case, you can make use of the “TO_DATE” function in Google Sheets.

The general syntax for the TO_DATE function is as follows,

=TO_DATE(value)

Here, you can replace the “value” argument with a number that needs to be converted to a date or cell reference containing the serial number.

Here are the steps to use the TO_DATE function,

  • Type “=to_date
  • Select the first option from the popup or pressTab” on your keyboard
  • Enter the serial number manually or select the cell reference value as “B2
  • Close the bracket using “)
  • Press “Enter” on your keyboard

How to set the default date format to the United States

Google Sheets formats date based on your locale. Look at the table below where the dates have been represented in Australian format as dd/mm/yyyy.

To change your location to the United States (mm/dd/yyyy), follow the steps below,

  • Hover to the main menu and click on “File
  • From the popup, choose “Settings
  • Click on the dropdown below the option “Locale
  • Select “United States” from the list
  • Click on the “Save and reload” button in the green

After following the above steps, all converted dates should be represented in the “mm/dd/yyyy” format. Refer to the following screenshot.

Conclusion

Most of the time, the DATEVALUE function is particularly helpful after downloading the datasets from various sources.

Dates are formatted and displayed in month, date, and year format (mm/dd/yyyy) to make it easy for humans to see and understand.

But note that dates are converted and stored as integer numbers in spreadsheet software like Google Sheets whenever you enter a new date.

You can use the DATEVALUE function to check if the entered dates are in the correct format.

The main benefit of the DATEVALUE function includes getting the serial numbers for those particular dates for future analysis and data organization. 

If you are stuck somewhere while using the DATEVALUE function or facing a particular error, feel free to comment below. I will respond to all of your questions as soon as possible.