To convert a date string into proper date format, you can use a formula in google sheets that will "translate" the string into a number. Then you can format the "date converted" column into a date on google sheets. For this, you will need another cell, as I have done below.
If you have a date stored as a string in Google Sheets and need to convert it to a proper date format, you can use a formula to achieve this. In this guide, we will walk you through the steps to convert a date string into a date format in Google Sheets.
- Create a new column in your Google Sheets where you want to store the converted dates. This new column will hold the formula to convert the date string into a proper date format.
- Insert the following formula into the first cell of your new column:=arrayformula( if( len(A2:A), datevalue(left(A2:A, 10)), iferror(1/0) ) )
- Modify the formula points to reference your original date column. For example, if your original date column starts at cell D2, modify the formula to:
- =arrayformula( if( len(D2:D), datevalue(left(D2:D, 10)), iferror(1/0) ) )
- Select the entire column containing the converted dates.
- Go to Format > Number in the menu bar.
- Select Date from the drop-down list.