How to Convert Text to Date in Google Sheets (3 Easy Ways)

In this tutorial, we will look at how to convert a text to date in Google Sheets. The importance of this transformation cannot be understated as many crucial calculations occur in spreadsheets around dates.

Table of Contents

The Importance of Converting a Text to a Date

The date is a numerical value that is notorious for being the type of data that gives the most errors in calculations to users in a spreadsheet. So, it is crucial to get things right.

This commonly happens when some dates are presented as text rather than numerical values. You can’t calculate with text values of course, for example, sorting a dataset chronologically.

Situations where a Date might be Interpreted as Text

There are three such instances where a date might be a text string unbeknownst to its user:

The dates are inserted in a different format. Google Sheets has its own localized format for recognizing dates. By default, it depends on the region you are in. If one user entered the date in their local format, chances are that that date will not be recognized in your region and thus be saved as text.
The date might have been saved as text. More often than not, for security or formatting reasons, users can save their dates as text. This can also be done by simply putting an apostrophe (‘) in front of the date.
The date could have been saved in an 8-digit format, “yyyymmdd”. This is commonly seen when extracting data from documents like PDFs.

In this article, we will show you how to resolve each of these issues in three separate approaches.

3 Ways to Convert Text to Date in Google Sheets

Check First! Determine if the Value is a Date or Not

It is always a good idea to check whether the dates you are working with are the correct time. We want numbers to be numbers, strings to be text, and dates to have a date value.

For dates, the text is quite simple. You only need to apply the ISDATE function on the cells containing dates. However, this function only checks the format of how the data is presented, not the underlying value type. So, even if a text has the correct date format, this function will return TRUE.

Thus, the best approach is to use the ISTEXT function if you suspect the dates are in the text format.

=ISTEXT(B2)

1. Change Location Settings to Convert Text to Date in Google Sheets

The first use that we mentioned is the mismatch of date format of different regions of the world. With Google Sheets tracking location data, it will only recognize the formats suited to your region.

So, if you import or work with a spreadsheet created in another region, the date format may not be recognized and seen as a text string.

Our default format is “MM/DD/YYYY”, but the date we have imported is in “DD/MM/YYYY” format. Thus, it is seen as text or gives the wrong date.

To remedy this issue, we must change the locale settings to match that of the given date.

Step 1: Navigate to Settings from the File tab. File > Settings

Step 2: In the Settings window, under the General tab, you will find Locale. By default, it is set to the United States, meaning the date format is MM/DD/YYYY.

We must change the Locale to the United Kingdom, or any other region that accepts the DD/MM/YYYY format.

Step 3: Click Save and reload to reload the worksheet with the updated settings.

Now, any date that is inserted or imported in the DD/MM/YYYY format will be correctly shown as a date.

2. Using DATEVALUE and TO_DATE Functions to Convert Text to Date

The second issue, and the primary objective of our article, we had was what to do when faced with dates that are saved as text. This is represented by the following image:

At first glance, the dates may look correct but when we go to use them in any numerical calculation, we will not get the correct results.

To remedy this, we use a combination of two functions: DATEVALUE and TO_DATE.

Even if our dates are presented as text (as seen by the ISTEXT check) the values themselves follow the correct date format. Meaning that they will be considered as dates even if you can’t use them in calculations. We can utilize the DATEVALUE function to extract the value of the date.

=DATEVALUE(B3)

Even though we have successfully converted the string date to a numerical date, we still have to present the value in the correct format. That’s where the TO_DATE function comes in.

What the function does is it converts a number to a date.

Our formula to convert the text value to a date in Google Sheets:

=TO_DATE(DATEVALUE(B3))

Problem: Datevalue parameter cannot be parsed to date/time

This is a common issue faced by many users when trying to find the value of a date with date formats that don’t match the locale.

Solution: Change the Locale settings from the Settings window (see method 1 of the article). It should not only give the correct value from the DATEVALUE function but also work with the intended date format.

3. Convert the 8-digit Number to Date in Google Sheets

For security purposes, most documents are usually saved as PDFs. These PDFs contain datasets that are usually imported to spreadsheets for use.

During import, date fields may be transformed into the following format:

This 8-digit value is none other than the date in the “YYYYMMDD” format.

To transform this numerical value into a proper date, we first have to split the values of the year, month, and day. Then apply them in the DATE function.

To get the Year:

LEFT(B3,4)

For Month:

MID(B3,5,2)

For Day:

RIGHT(B3,2)

Now, combining all these functions inside the DATE function:

=DATE(LEFT(B3,4),MID(B3,5,2),RIGHT(B3,2))

Note that the values in the Year, Month, and Day columns are in string form. With the help of the DATE function, we were able to convert those text values to a proper date in Google Sheets.

Final Words

That concludes our guide to converting text to date in Google Sheets. The crux of the conversion lies in the formatting of the dates, which can be set from the settings or updated through formulas.

Feel free to leave any queries or advice you might have for us in the comments section below.