In this tutorial, you will learn how to convert text values to date values in Google Sheets.
How To Convert Text To Date in Google Sheets
Sometimes you may find yourself with a dataset that contains dates in a text format. This can be an issue if you want to use these dates in calculations. Dates saved as text will also prevent users from formatting their dates in their preferred format.
Fortunately, Google Sheets provides two useful functions that can help you convert text to date formats: the TO_DATE and DATEVALUE functions.
If your text saves your date as an 8 digit number, such as “20230101”, Google Sheets may not recognize it as a valid date string. We can use the LEFT, MID, and RIGHT functions to extract the year, month, or day from a date saved in such a format.
In this guide, we will further explain how to use these functions to transform text into dates in Google Sheets.
How To Convert Text Strings To Different Date Formats in Google Sheets
Here’s how to convert text strings to different date formats in Google Sheets.
Step 1
First, we’ll use the DATEVALUE function on our text value.
The DATEVALUE function takes a single argument and returns an integer that represents the date’s value.
All dates in Google Sheets are stored as an integer. This allows Google Sheets to perform calculations between dates. The numbers start with January 1st, 1900, represented by a ‘1’. The numbers then increment by 1 for every day after that.
Step 2
We’ll use the DATEVALUE function to find the serial number that corresponds with the valid date string.
In our example, we were able to convert the text “2023-01-02” into the serial number 44928.
Step 3
Now that we have our date values, we’ll use the TO_DATE function to convert the serial numbers into an actual date.
Step 4
Next, we’ll use number formatting to display our date how we want to.
Start by selecting the output of our TO_DATE formula.
Step 5
Next, click the option Format > Number > Custom date and time.
Step 6
A dialog box will pop up that shows multiple common date and time formats.
You may select one of these options or choose to type your own custom format through the provided textbox above. Click on Apply to proceed.
Step 7
Your range of date values should now follow your desired date formatting.
Step 8
You may encounter difficulty using the above method when working with 8-digit strings that represent dates. This is because Google Sheets does not consider this format a valid date string.
However, we can still convert these types of text values into dates.
The key functions for this step are the LEFT, MID, and RIGHT functions. These functions allow us to extract substrings from a given text value.
For example, LEFT(“20230102”,4) will return the first four digits from our string which corresponds to the year section of our date.
Similarly, the RIGHT function allows us to return the last n characters from a string. For example, RIGHT(“20230102”,2) will return “02”.
Lastly, the MID function allows us to extract substrings found somewhere in the middle of the string.
After extracting the year, month, and day from our string, we can add them as arguments to the DATE function to output a valid date.
Summary
This guide should be everything you need to convert text to dates in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.