Working with dates in Google Sheets can be challenging at times. Dates in spreadsheets can sometimes be formatted as numbers or text.
If you’re trying to organize your data or do any computations with these dates, this will be an issue.
Fortunately, there is a method for converting your words to dates.
Converting Text to Date with DATEVALUE and TO_DATE Functions
In order to perform our task, here we are going to combine these two functions to get the desired results. So first let’s understand these functions individually.
The DATEVALUE function accepts a date value and returns a serial number that represents that date.
The DATEVALUE function has the following syntax:
=DATEVALUE(date string) , here
date string – the date is represented by this string. Within this function, you can use any date format. This function allows you to use cell references. If the string is entered directly into the formula without a cell reference, it must be enclosed in quotation marks.
A value is converted to a date using the TO DATE function.
The TO DATE function has the following syntax:
value – this is the cell reference or value you’d like to convert to a date.
If the value is a number, it is transformed to a date and the number of days since December 30, 1899 is counted.
The number of days preceding this date is considered as a negative value.
Now, to get the desired results, lets combine these two through an example.
Here, I have text values in column A, which are converted to dates using this formula. This works because DATEVALUE converts my value to a number, which is then converted to a date by the TO DATE function.
Converting 8 digit number format to date
When we import data into our sheet sometimes the format of these dates are inappropriate and are arranged like large numbers. Thus, in such situation, we would have to extract date, month and year to make it properly look like a date.
To do this we will use a formula whose syntax will be combination of DATE, RIGHT, LEFT and MID. Lets take an example to make you understand this,
- The year is extracted first from my data in cell A2, which is 06102021. The four digits starting on the right of my value, “2021,” are taken into my calculation RIGHT(A2,4).
- After that, we must return the month. I have LEFT in my formula (A2,2). This will take the two digits on the left of my formula, which equals 06, and multiply them together.
- We must now restore the day. This is what MID(A2,3,2) performs at the end of my formula. This component of the formula starts at the third digit and subtracts two digits, giving a result of 10.
- Finally, the DATE function wraps my formula, which converts the value to a date.
So here’s the final formula:
Thus, you have been made aware of two different ways to convert text to dates in your spreadsheet in this post. If you’re having problems, hopefully one of these two techniques can help you find a solution.