And sometimes, you can come across dates that are formatted as text (or look like dates but are not in the acceptable date formats, so Excel treat them as text strings). Since dates are stored as numbers in the backend in Excel, it allows you to format dates in different ways as well as use them in calculations. But when a date is stored as a text string in Excel, you won’t be able to use it in calculations or format it like a date. In this tutorial, I will show you how to convert text into dates in Excel. I will cover various scenarios where you may encounter a date that has either been formatted as text or is entered as a text string (and Excel doesn’t recognize that it’s a date).
Regular Date vs Text Date in Excel
Before I get into the methods on how to convert text to date in Excel, let me first explain what’s the difference between a date that is an acceptable format in Excel, and a date that is in the text format. As I mentioned earlier, dates are stored as numbers in the back end in Excel, which makes it possible for us to use them in calculations just like numbers. This also makes it possible for us to format dates and show it in different ways, which cannot be done when the date is entered as a text string or formatted as a text. To give you an example, 01-Jan-2023 is an acceptable date format in Excel and if you enter this in a cell, it would be shown as a date, but it would be stored as 44927 in the backend (where 44927 is the number corresponding to the date 01-Jan-2023). However, if you enter 01.Jan.2023, it would be entered as a text string because this is not an acceptable date format. This also means that you cannot use this in calculations because this has not been stored as a number in the back end Below I have the table where I show the difference between an acceptable date format and a date in text format or entered as a text string. Now let’s look at some of the methods you can use to convert text to dates in Excel
Convert Text to Date in Excel (with acceptable Date Formats)
First, we’ll look at scenarios where you have dates that are considered acceptable date format in Excel but are formatted as text in the cells. For example, 01-Jan-2023 is an acceptable date format, and in case it has been formatted as text, you will be able to use the methods shown in this section to convert it back to date. However, if a date is entered in a format that is not an accepted date format (such as 01.Jan.2023 or 8th Jan 2023), you will have to use methods covered later in this tutorial.
Using the DATEVALUE Function
Often, when you download your data from a database or you copy your data from a web page, you would notice that the dates have been formatted as text. If your date is in the acceptable date format but has been formatted as text, you can easily convert it into the corresponding serial number by using the DATEVALUE function. And once you have the number, you can easily format it as a date. Below I have a data set where I have dates in column A that have been formatted as text and I want to convert these back to regular dates. This can be done using the below DATEVAUE formula: Copy this formula to all the cells in column B. When you use this formula, it gives you a numeric value, which corresponds to the date in cell A2. So while we have got the text date converted into a number, we still need to format it so that it shows up as a date in the cell. Below are the steps to format these numbers in column B to date: The above steps would convert the text that you have in column A into serial numbers using the DATEVALUE function, and then these are formatted to show up as dates. In case you need more control over how the dates are formatted, you can also use the Format Cells dialog box, which has a lot more date formatting options, as well as the option to create your own date format.
Using the VALUE Function
While the DATEVALUE function is built specifically to deal with dates that have been formatted as text, there is also a VALUE function that can deal with dates as well as non-dates. VALUE takes any number that has been formatted as text and converts it back into numbers. Below I have a data set where I have dates in column A that are in the text format, and I want to convert these back into regular dates. Below is the VALUE formula that will do this for me: Copy this formula to all the cells in column B. And if you see the result as numeric values and not dates, you can format the cells to show these in dates using the below steps:
Using Mathematical Operators (Add, Multiple, Double Minus, Division)
If you have a date in the format that Excel understands (even if it is in the text format), you will be able to use it in simple arithmetic calculations (such as addition or subtraction or multiplication or division). We can use simple arithmetic calculations to convert the date that we have in the text format back into numeric format. All you need to make sure is that you don’t end up changing the date in the calculation (which won’t happen if you add zero to the date or multiply the date by 1). Below I have a data set where I have the dates in the text format in column A (but note that these are the dates that Excel would have considered as proper dates had they not been in the text format). And here are some simple arithmetic calculations you can use in the adjacent column to get the numeric value of the date. or or or You can use any of the above formulas and it would give you a numeric value in column B as shown below. Note that we have the numeric value of the date in column A comma we can convert these back into date format. Below are the steps to convert these numeric values into dates: In case you want to customize the date format further, select the cells in column B and then use the keyboard shortcut Control + 1 to open the Format Cells dialog box. Here you will get more date format options and you can also create your own custom date format if you want.
Using Paste Special
As explained in the previous method, you can convert a date that is formatted as text back into a numeric value by adding a zero to it. While I’ve used a formula and got the result in a separate column in the above method, you can also do the same thing using the Paste Special dialog box. Below I have a data set where I have the dates in the text format in column A and I want to convert these back to dates: Below are the steps to convert dates in text format back into dates using the Paste Special technique: The above steps would instantly convert all the dates in column A into numeric values. Now you can select these cells and change the format to show these numbers as dates. How does this work? When I copy a cell and then use paste special to add that cell to the dates in column A it’s essentially adding zero to these dates. This addition does not change the numeric value of the date but it does convert the date value which is formatted as text into a numeric value that represents that date in the back end of Excel.
Convert Text to Date (with Format Not Recognized as Dates)
In all the examples covered above, I’ve shown you how to convert a date that is in the text format back into a date. But so far, all our dates were in a format that Excel actually recognizes as a proper date. It’s just that they have been formatted as text, so all we had to do was change the format back to date. In this section, I’m going to show you some techniques that you can use when you have dates that are not recognized by Excel proper dates. Such datasets often need more work, as we need to manipulate the data and convert it into proper date formats
By Changing the Delimiters in the Date
When there are dots or spaces instead of forward-slash or hyphens in a date, Excel would consider it as a text value as this is not an acceptable date format in Excel. Below I have data set where I have the dates in column A where there are dots in between the day, month, and year number. If you try to use the DATEVALUE or the VALUE function on this data, it will give you a value error (as Excel does not know how to convert this text string into a numeric value). So the only way to convert this text value into data is by replacing the dot and changing the date into a proper date that Excel can recognize as a date. Let me show you three different ways you can use to change the delimiters in the date in column A so that it can be used as a proper date in Excel.
Find and Replace
With Find and Replace, we can replace the dots in the date with a forward slash or hyphen/dash so that it will become a proper date format. Below I have dates in column A that have dots as delimiters and I want to replace them with a forward slash or hyphen. Here are the steps to do this: The above steps would instantly change the dots in the dates into dashes, making the text values in column A into dates. Note that the dates also aligns to the right (while it was aligned to the left when it has dots instead of dashes). Pro Tip: You can use the keyboard shortcut Control + H to open the ‘Find and Replace’ dialog box
Using Text to Columns
Another quick way to convert dates that are in text format into proper dates is by using the Text to Columns option. Below I have a dataset where I have dates in column A that have dots as the delimiters and I want to convert these into regular dates that are recognizable by Excel. Below are the steps to do this using Text to Columns: The above steps would instantly give you the dates in column B It’s possible that the dates that you get in Column B are not in the format that you want. So once you have the result, you can change the format by selecting the cells in Column B, holding the Control key, and pressing the 1 key. This will open the Format Cells dialog box where you can change the format of the dates.
SUBSTITUTE Function
You can also use the SUBSTITUTE function to replace the dots or dashes or any other delimiter with a forward slash or dash. While the result of the SUBSTITUTE formula would be a text string, since it would be in an acceptable date format, we would be able to use DATEVALUE function to convert it into the numeric value of the corresponding date. And once we have the numeric value, we can then format the cells to show the date in any format we want. Below I have the data set where I have dates in column A that have dots as the delimiters, and I want to convert these into proper dates. Build the formula that would give us the numeric value that represents the proper date: Enter this formula in cell B2 and then copy it for all the other cells. Once you have the results in column B, follow the below steps to convert these numbers back into dates: In case you want the dates to be shown in any other format, open the format cells dialog box and then choose from the multiple inbuild date formats or create your own custom date format. Note that in the above formula, I had dots in the dates that I wanted to replace with dashes. In case you have any other delimiter (such as space), you need use that delimiter instead of the dot in the formula.
Convert Two Digit Year Text Date to Proper Date By Using Error Checking
Sometimes you may get a data set where the year is represented by two digits instead of the four digits in the date. For example, instead of 01-Jan-2023, it would be 01-Jan-23 Thankfully, it only takes a click to get this sorted. Below I have a data set in column A where I have the dates with two-digit year values and I want to convert these into proper date formats. Here are the steps to do that: The above steps would change the dates and convert the two-digit year value to a four-digit year value. Note that you can only use this if all the dates in your data set are of the same format (19XX or 20XX). In case there is a mix (for example in the case of date of birth), you will not be able to use this method
Using TEXT formulas
Sometimes you may get dates that have some additional text in the same cell. For example, you can get a date where it includes the day name (such as Saturday, 08 Oct 2022) or a suffix after the day value (such as 8th October 2023 or 2nd October 2023). In these cases, you first need to use some text formulas to clean the data and remove these unwanted text strings, and then try and convert these dates into their corresponding serial number (which can be done using the DATEVALUE function). Let me show you a couple of common examples and how you can do this using simple text formulas:
When You Have Day Name in the Date
Below I have a data set where I have the day name before the date in column A, and I want to convert these dates in column A into proper date format. Here is the formula that will do this for me: Enter this formula in cell B2 and then copy it for all the cells in column B. In the above data set, the day name is always followed by a comma and a space character. Since this is the same in all the cells in the data set, I have used the above formula to first identify the position of the first space character (using the FIND function). Once I know the position of the first space character, I used the RIGHT function to extract everything which is to the right of that space character. And since the result of the RIGHT function is still a text string, I’ve used the DATEVALUE function to get the serial number of the date. Once you have the serial number of the date, you can format the cell to show it in the short or long date format (your option is available in the Home tab in the formatting drop-down)
When You have Suffix After the Day Value in the Date
Below are the data set where I have a suffix (such as ‘th’, ‘rd’, ‘nd’, ‘st’) after the day value. Because of this, the date is considered as a text string in Excel, and to convert this date in the text format back into the date format, I will have to remove the suffix from these cells. Below is the formula that will do this for me: Enter this formula in cell B2 and then copy it for all the cells in column B. In the above formula, I have used the SUBSTITUTE function to remove each of these suffix values and replace these with blanks. Since there are four such values (th, st, rd, nd), I had to use the SUBSTITUTE function four times. The result of one SUBSTITUTE function is fed into another SUBSTITUTE function so that in the end we get the result where any of these four suffixes would be removed. Once the suffixes are removed, we have used the DATEVALUE function to get the serial number of the date so that we can convert it back into the proper date format
Convert 8 Digit Date in MMDDYYYY format to Date
Below I have a data set where I have the dates in the DDMMYYYY format, where there is no space or delimiter between any of the digits. Since the above dataset follows a consistent pattern (where the first two digits are always the day value, the next two digits are the month value, and the last four digits are the year value), we can use simple text formulas to extract these digits and specify them as the day, month and year values. Here is the formula that will do this for me: Enter this formula in cell B2 and then copy it for all the cells in column B. The above formula uses the RIGHT, MID, and LEFT functions to extract the year, month, and day values respectively, And then these are used within the DATE function to construct a proper date in Excel. In this tutorial, I showed you how to convert text to date in Excel using various techniques. The method you use would depend on whether your date is in a format that Excel would recognize as a proper date or not. If it is in a proper date format and is being considered as a text string in Excel, you can use formulas such as DATEVALUE or VALUE to get the serial number of the date and then format it to show as a short or long date. You can also use simple arithmetic operators or Paste Special operation techniques to quickly convert dates that are formatted as text into serial numbers corresponding to that date. And in case your dates are in a format that is not recognized by Excel a date, you will need to do some text manipulation using simple text formulas or functionalities such as Find and Replace or Text to Columns. I hope you found this Excel tutorial useful. Other Excel tutorials you may also like:
How to Compare Dates in Excel (Greater/Less Than, Mismatches)Combine Date and Time in Excel (Easy Formula)Convert Date to Text in Excel – Explained with ExamplesHow to Convert Serial Numbers to Dates in ExcelConvert Text to Numbers in ExcelHow to Convert Numbers to Text in Excel
