Return to Excel Formulas List

Convert Text String to Date – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to convert a text string to a date in Excel and Google Sheets.

convert text to date Main Function

Convert Text String to Date – DATEVALUE or VALUE Functions

If you have a date in Excel stored as text, you will not be able to use the date in any calculations.  You can convert a date stored as text to an actual date in Excel by using the DATEVALUE Function.

DATEVALUE

Dates in Excel are stored as a value representing the number of days since the first day of Excel.

The first day of Excel is the imaginary date of January 0, 1900. So the date 05/08/1995 is stored as the number 34,916.

Once you have done that, you can format the date value to show as it was originally being displayed.

DATEVALUE 1

The VALUE Function will work in the same way

Convert Text ‘yyyymmdd’ to Date

There might be a time that you need to convert a number or a text string in the format yyyymmdd to a date. To do this, you will need to use the DATE function, in conjunction with the LEFT, MID and RIGHT Functions.

YYYMMDD

The LEFT Function

The LEFT function will return characters on the left of your text string, in this case we have selected to return 4 characters.

The MID Function

The MID Function will return middle characters in your text string.  The starting middle character is indicated by the second argument (eg: 5), and the length of the string is indicated by the third arguments (eg: 2).

Left Mid Right

The RIGHT Function

The RIGHT function will return the characters to the right of your text string, in this case we have selected to return 2 characters.

The DATE Function

The DATE function will then take the 3 extracted values and combine them to become a valid date, eg: DATE(1995,08,05) where the values are replaced by the LEFT, MID and RIGHT functions.

 

Convert Text String to Date – DATEVALUE or VALUE Functions in Google Sheets

The DATEVALUE and VALUE Function work the same way in Google Sheets as they do in Excel.

convert text to date-G1

Convert Text String to Date – DATE, LEFT, MID and RIGHT Functions to get a Date Value in Google Sheets

The DATE, LEFT, MID and RIGHT Functions in order to get a date value from a text string work the same in Google Sheets as they do in Excel.

convert text to date G2