See all How-To Articles

Convert String to Date With Text to Columns in Excel & Google Sheets

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 string to date final data

 

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.

 

convert string to date initial data 1

 

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.

 

convert string to date text to columns

 

2. In Step 1 of the Text to Columns Wizard, leave the default option (Delimited), and click Next.

 

convert string to date text to columns 1

 

3. In Step 2, uncheck all delimiters, and click Next.

 

convert string to date text to columns 2

 

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.

 

convert string to date text to columns 3

 

Finally, in Column C, you get all dates from Column B formatted as dates, rather than text.

 

convert string to date final data

 

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:

=DATEVALUE(B2)

 

google sheets convert string to date datevalue function

 

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).

 

google sheets convert string to date datevalue function 2

 

As a result of this step, the formula from C2 is copied to the range C3:C7.

 

google sheets convert string to date datevalue function 3

 

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.

 

google sheets convert string to date datevalue function 4

 

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.

 

google sheets convert string to date datevalue function final