ExcelInsider Team
Sometimes, data imported into Google Sheets appears to be a date but is actually formatted as plain text. This issue commonly arises when data comes from external sources, forms, or copy-pasted content. When dates are stored as plain text, it prevents proper sorting, filtering, or using functions. Converting text to an actual data format in Google Sheets is easy using formulas, and formatting your sheet correctly ensures everything works as expected. In this tutorial, we will learn some simple functions and formulas to convert text into proper date format.
Steps to convert text to date format using a formula:
➤ Label cell E1 as Converted Date
➤ Go to Format > Number > Date.
➤ In E2 type the formula
=DATE(LEFT(D2,4), MID(D2,5,2), RIGHT(D2,2))
➤ Click Enter and drag it from E2 to E11 to fill the range (E2:E11).
In this article, we will explore multiple practical methods to convert text to date in Google Sheets. We will use functions like DATE, LEFT, MID, RIGHT, ARRAYFORMULA, TO_DATE, and even DATEVALUE to transform raw text into readable and usable date values.
Using DATE with LEFT, MID, and RIGHT Functions to Convert Text to Date Format
This is one of the simplest and one of the most reliable methods to convert a raw text date like 20240518 to a valid date format in Google Sheets. The DATE function creates a valid date using three components from a YYYYMMDD string. We use the LEFT, MID, and RIGHT functions. By combining them, we can build a clean date recognized by Google Sheets.
Steps:
➤ Select column F and go to Format > Number > Date.
➤ Insert a new column (E) next to the Text date (Raw) and name it Converted Date.
➤ In E2 type the formula
=DATE(LEFT(D2,4), MID(D2,5,2), RIGHT(D2,2))
➤ Press Enter and copy the formula down the column to apply it to the rest of the rows.
➤ It will return all the dates in date format.
Format The Output Using TO_DATE Function
When your date appears as a number instead of a readable format, use TO_DATE function. It converts numeric date values into proper date displays, making your data easier to read and work with.
Steps:
➤ Insert a new column (E) next to the Text Date (Raw) column and name it Converted Date.
➤ Select column E and go to Format > Number > Date.
➤ Select cell E2 and enter this formula:
=TO_DATE(DATE(LEFT(D2,4), MID(D2,5,2), RIGHT(D2,2)))
➤ Press Enter and drag it from E2 to E11 to fill the range (E2:E11).
➤ It will return the date in date format.
Convert All Dates At Once Using ARRAYFORMULA
If you have a column of raw text dates like 20240518, you can convert them into proper date format using a combination of DATE, LEFT, MID, RIGHT, and TO_DATE. You don’t need to drag the whole column here; it will automatically fill in the proper date.
Steps:
➤ Go to Format > Number > Date to ensure correct display as it was shown in the first method.
➤ Select column E and name it Converted Date.
➤ Enter the formula
=ARRAYFORMULA(IF(D2:D=””, “”, TO_DATE(DATE(LEFT(D2:D,4), MID(D2:D,5,2), RIGHT(D2:D,2)))))
➤ Press Enter.
➤ It will automatically fill the rest of the cell of the E column with the proper date.
Using DATEVALUE Function to Convert Raw Text to Date
The DATEVALUE function in Google Sheets converts date-formatted text into actual date values that can be used in formulas, charts, or sorting.
Steps:
➤ Select column E and name it Converted Date, and in E2 paste the following formula:
=DATEVALUE(LEFT(D2,4) & “-” & MID(D2,5,2) & “-” & RIGHT(D2,2))
➤ Press Enter.
➤ Drag it from E2 to E11 to fill the range (E2:E11). It will return the text in actual date.
Frequently Asked Questions
Why does it show a 5-digit number instead of a date?
That’s a date serial number. Just go to Format > Number > Date to fix it.
Will it work if the column has blanks?
Yes. Use ARRAYFORMULA with IF function to skip blank rows.
Can I reverse the format to DD-MM-YYYY?
Yes. Use the formula =TEXT(…, “dd-mm-yyyy”) to customize the format.
Can I convert a full column of dates from text to actual date format at once?
Yes. Use ARRAYFORMULA:
=ARRAYFORMULA(IF(A2:A=””, “”, DATE(RIGHT(A2:A,4),MID(A2:A,3,2), LEFT(A2:A,2))))
Wrapping Up
Converting text to date in Google Sheets is essential when working with imported or manually entered data that doesn’t follow standard format. In this article we explored several easy and effective ways to convert text to date in Google Sheets.
