Google Sheets datetime

Working with dates in Google Sheets can sometimes be quite frustrating. This is because there are so many ways in which dates can be represented.

The problem becomes bigger when you want to perform operations that involve these dates, like sorting by date or performing calculations that involve dates.

In this tutorial, I will show you how to convert Text to Dates in Google Sheets.

Why Convert Text to Dates?

We often find calculations involving dates that end up giving unexpected results. This usually happens when a value looks like a date but is actually interpreted as a text value by Google Sheets.

You might also come across this issue when you import data from external sources or convert a PDF file to Google Sheets.

In such situations, the right thing to do would be to convert the text values so that Google Sheets interprets them as dates.

In this tutorial, we will show you how you can tell if a value is actually seen as TEXT or DATE and how to convert text values to date in Google Sheets

How to Find out if a Date is actually a Text Value?

An easy way to understand if a value in a cell is actually a date or not is by using the ISDATE function.

If the value is a date accepted by Google Sheets, the function returns TRUE, otherwise, it returns FALSE.

Let us say we have dates as shown in the following screenshot:

To understand if the cell A2 contains an actual date or just text values in disguise, type the following formula in a blank cell:

Since the date in cell A2 of our example is not accepted by Google Sheets as a date value (for our location), the function returns FALSE.

The same formula, if applied to cell A3, however, will return TRUE, since it is a valid date.

There are many other ways to tell if the date is actually a TEXT value:

• You would notice the contents of the cell aligned to the left (if the width of the cell is more than the width of its contents). A date value is always aligned to the right.
• In some cases, when you click on the cell, you will see a leading apostrophe to the date in the formula bar, signaling that it is a text value.

How to Convert Text to Date in Google Sheets

Once youve determined that the date is actually interpreted by Google Sheets as a text value, you can convert it to date.

The method to convert text to date depends on your situation. Here are some situations where a date might be interpreted as text in Google Sheets:

• A previous user might have worked on the sheet and saved the date values as text, either using the apostrophe () before the date value or converting the date to plaintext format.
• Your dates might be entered in a format that is different from the date format accepted by your version of Google Sheets. Date formats understood by Google Sheets depends on the region and language setting. For example, you might have a date with the dd-mm-yyyy format, when Google Sheets only accepts dates with the format mm-dd-yyyy in your region or language setting.
• You might have imported a PDF into Google Sheets and found the dates to be in a weird text form, something like whats shown below:
  

Let us take a look at all three of the above scenarios in three separate examples.

Using DATEVALUE and TO_DATE Function to Convert Text to Date in Google Sheets

We can use a combination of the DATEVALUE and TO_DATE functions to convert a date that is originally in TEXT format to one that is in the Google Sheets DATE format.

The DATEVALUE function takes a date value in any valid format and returns the same date as a serial number.

Note: By default, all dates in Google Sheets are stored as an integer or serial number, which makes it easy to use them in calculations. Serial numbers start January 1, 1900, and increment by 1 for every day after that. So January 1, 1900 is serial 1, the next day is serial 2, and so on.

The syntax for the DATEVALUE function is as follows:

Here, date_string is the date in TEXT format. The strings accepted by this function include any date that is normally auto-converted when entered, without quotation marks into a cell. The date also needs to be in a format that is understood by Google Sheets in your region and language settings.

The TO_DATE function converts a given serial number to its corresponding date (in the Google Sheets DATE format). The syntax for the TO_DATE function is as follows:

Here, the value is the number to be converted to a date. It can be a numerical value or a reference to a cell containing the numerical value.

Let us take an example. Lets say you have the following date:

Notice there is a leading apostrophe, which means that this is a TEXT value. To convert this date in TEXT format to a proper DATE value, you need to use the TO_DATE function, along with the DATEVALUE function, as follows:

The DATEVALUE function will convert the text date to a number value, and the TO_DATE function will take this number and convert it back to an actual date, as shown in the image below:

Changing your Spreadsheets Location or Language Settings to Convert Text to Date

If you think the issue might be the region or language settings for your Google Sheets, there are two options.

You can either change your date to a format acceptable in your locale setting or change the spreadsheets location settings.

You can change the accepted date formats as follows:

1. Navigate to File->Spreadsheet Settings.
   
2. Select the General tab
3. Under Locale and Time Zone, select your required setting.
   
4. Click Save Settings.

By setting the locale and time zone of your spreadsheet, you are changing the default currency, date and number formats.

Converting Text to Date when Date is in an 8-digit Number Format

If your Google Sheet is imported from a PDF, it is not uncommon to find the dates formatted in a weird text form, like for example, an 8-digit number as shown below:

In such cases, the above two techniques will not work. You will need to extract the day, month and year from the given text date and feed them into the DATE function, to obtain a DATE value in a valid format: DATE(year, month, day)

Here are the steps to convert the given 8-digit text date to an actual DATE value:

1. Extract the year. This comprises the last 4 digits of the text date (which are to the right of the value). To do this, we use the RIGHT function as follows: RIGHT(A2,4)
2. Extract the month. This comprises the 3rd and 4th digits of the text date (which are in the middle of the value). To do this, we use the MID function as follows: MID(A2,3,2). Here we are extracting 2 digits starting from the 3rd digit onwards.
3. Extract the day. This comprises the first 2 digits of the text date (which are to the left of the value). To do this, we use the LEFT function as follows: LEFT(A2,2)
4. Finally, combine these together into the DATE function, to create a DATE value with the extracted day, month, and year values.

So the final formula will be:

This should produce the date in the correct format as shown below:

Its important to note that not all text dates are imported in exactly this format. The order of the day, month, and year might vary from case to case.

The trick is to effectively use the RIGHT, LEFT, and MID functions to extract the right numbers to represent day, month, and year.

Following are some more possible text date formats, along with the corresponding formula that can be used in each case, to convert them to actual dates:

Notice in the last formula of the above screenshot, the year value in the original text date contains just 2 digits (instead of 4).

So to get the year 2020 we concatenated 20 with the year value by using the formula: 20&RIGHT(A4,2). If we dont do this, Google sheets will return the value 1920 for the year instead of 2020.

In this way, you can convert dates put in any format by effectively extracting the day, month, and year values and passing them through the date function.

In this tutorial, we showed you three cases where you might find dates represented as TEXT in Google sheets.

We also showed you how to convert these TEXT values to actual DATE values. We hope you found this tutorial helpful.

You May Also Like the Following Tutorials:

How to Quickly Split Text in Google Sheets

5 Tips to Calculate Days Between Dates in Google Sheets

How to Set Print Area in Google Sheets

How to Add Days to Date in Google Sheets