In this tutorial, you will learn how to convert a string in a cell to a date by using Text to Columns in Excel and Google Sheets.
Convert a Text String to a Date
If you have dates in Excel formatted as text, you can easily convert them to date format using the Text to Columns functionality. This is often the case when you export data from another system. The data don’t usually have the appropriate formats. Say you have the list of dates shown below in Column B, all formatted as text.
The goal is to get dates from Column B formatted consistently as dates (M/D/YYYY) in Column C.
1. Select a data range (B2:B7) and in the Ribbon, go to Data > Text to Columns.
2. In Step 1 of the Text to Columns Wizard, leave the default option (Delimited), and click Next.
3. In Step 2, uncheck all delimiters, and click Next.
4. In the last step of the Wizard, select Date (MDY). In the Destination box, enter the cell where you want to position the new data (C2), and click Finish.
Based on Date type selected, Excel recognizes dates from the given text and formats them appropriately. The default date format is M/D/YYYY, so leave it as is.
Finally, in Column C, you get all dates from Column B formatted as dates, rather than text.
Convert a Text String to a Date in Google Sheets
In Google Sheets, there is no option to get dates from text cells using Text to Columns. You have to use the DATEVALUE Function to convert strings to dates.
Note: The DATEVALUE Function is also available in Excel and works in exactly the same way as explained below.
1. In the cell C2, enter the formula:
The result of the formula is 44,280.
The DATEVALUE Function returns a number for a given date. In Excel, dates are stored as numbers, starting from 1/1/1900. Therefore, 3/25/2021 is actually the 44,280th day after 1/1/1900.
2. Position the cursor in the bottom right corner of C2 until the black cross appears, and drag it through the end of the data range (C7).
As a result of this step, the formula from C2 is copied to the range C3:C7.
Note: The DATEVALUE Function recognizes only strings that have the date format MM/DD/YYYY. As you can see, the formula in cells C3 and C5 returns a value error (#VALUE!), since text in B3 and B5 are not in the right format.
3. Now format values in Column C as dates to display dates in a proper format. To do this, select all cells in a range (C2:C7), and in the Menu, go to Format > Number > Date.
As a result, all text cells from Column B (that have dates with valid formats) are now copied to Column C, and formatted as dates.
For this specific data, a Find and Replace All to replace periods with slashes would get rid of the errors.