How to Convert String to Date in Excel & Google Sheets

This tutorial demonstrates how to convert a string in a cell to a date by using Text to Columns in Excel and Google Sheets.

 

 

► Click here to jump to the DATEVALUE Function section of this tutorial. It focuses on Google Sheets, but the same info applies to Excel.
► Click here to learn more about using the DATEVALUE Function in Excel.

Convert a Text String to a Date

If you have dates 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 the 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, choose Date (MDY). In the Destination box, enter the cell where you want to position the new data (C2), and click Finish.
   With Date chosen, 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, type the formula:

=DATEVALUE(B2)

 

 

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,280^th day after 1/1/1900.

2. Position the cursor in the bottom-right corner of C2 until a 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 isn’t in the right format.

3. Now format values in Column C as dates to display dates in a proper format. 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.

Note: For this specific example, find and replace all periods with slashes to get rid of the errors.