Skip to Content

How to Convert Text to Date in Google Sheets

Sometimes working with dates in Google Sheets can be a little troublesome. Sometimes dates can end up formatted as numbers or text in your spreadsheet.

This will be a problem if you are trying to sort your data or perform any calculations using these dates.

Luckily there is a way to convert your text to dates.

In this tutorial, I will show you how to convert text to date in Google Sheets.

Table of Contents

How to Convert Text to Date

Convert Text to Date with DATEVALUE and TO_DATE Functions

One of the easiest ways to change your text into date format in Google Sheets is by combining two functions: DATEVALUE and TO_DATE.

The DATEVALUE function takes a date value as an input and returns a serial number representing that date.

Here is the syntax of the DATEVALUE function:

=DATEVALUE(date_string)

  • date_string – this is the string that represents the date. You can use any date format inside this function. Cell references can be used in this function. If the string is directly put inside the formula without a cell reference you need to wrap your string in quotation marks

The TO_DATE function will convert a value to a date.

The syntax of the TO_DATE function is:

=TO_DATE(value)

  • value – this is the cell reference or value that you want to be converted to a date
    • If the value is a numeric value it is converted to a date which starts counting as the number of days since December 30, 1899
    • Negative values are counted as the number of days before this date

To combine these functions, you will be nesting them together like this:

=TO_DATE(DATEVALUE(value)

Here is an example using this formula in our spreadsheet.

convert text to date example

In column A, I have text values and with this formula, they are converted to dates. This works because DATEVALUE will take my value and convert it to a number and then the TO_DATE function will convert this number to date.

Converting an 8-Digit Number Format to Date

Sometimes when you import files into Google Sheets from other documents, the dates may end up formatted weirdly.

For example, you may get your dates showing up as an unwanted 8-digit number format like this:

convert 8 digit number format-1

The method we used above won’t work on something like this. To convert this to date, we will need to use a formula to extract out the year, month, and day individually to convert it to date.

To do this, we will be using a combination of DATE, RIGHT, LEFT, and MID.

Here is an example using the finished formula to convert something in a “mmddyyyy” format to a date

convert 8 digit number format-2

Here is a breakdown of why this formula works:

  • This formula firstly extracts the year from my data in cell A2 which is 06102021. Inside my formula RIGHT(A2,4), takes the four digits starting on the right of my value which is “2021”
  • Next, we need to return the month. In my formula, I have LEFT(A2,2). This will take the 2 digits on the left of my formula which returns 06
  • Now we need to return the day. At the end of my formula, MID(A2,3,2) does this. This part of the formula starts at the 3rd digit and takes 2 digits, so it returns 10.
  • Lastly, my formula is wrapped in the DATE function will convert the value to a date.

So my final formula is:

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,3,2))

To get a better understanding of how each step of this formula evaluates, take a look at this example:

convert 8 digit number format-3

When importing from other files you may get other date formats outside of just a “mmddyyyy” format. You will then have to combine your RIGHT, LEFT, and MID functions together in different ways to get your date formatted properly.

Here are a few other possible formats and ways to use these functions to get your dates formatted properly:

convert 8 digit number format-4

Closing Thoughts

In this guide, I have shown 2 different ways to convert your text to dates in your spreadsheet. Hopefully, if you are experiencing issues, one of these two methods will provide the solution that you need.

The second method seems a little complex at first but once you understand how each individual function works, it is really quite simple.

More Google Sheets Tutorials:
How to Insert Current Date
How to Sort by Date
How to Use the ISDATE function
How to Use the WEEKDAY Function
How to Convert Timestamp to Date