Sometimes you'll find yourself working with dates in an Excel spreadsheet that have been pasted or imported into Excel from another datasource. When that happens, Excel can treat those dates as text - in other words, they look like dates but don't behave like dates. For example you can't sort by date properly. This lesson looks at several ways you can convert a date which Excel is treating as text into a proper date value in Excel.

Use the DATEVALUE function to convert text to a date.

The DATEVALUE function takes a text value and tries to convert it into a date. Whether it succeeds will depend on the text value you are trying to convert.

Date problems often occur when you import data from a CSV file (which is a text file format) and Excel doesn't recognise the dates in the file so it imports them as text instead. Another scenario is when you copy and paste date values into Excel from another file format.

Two tell-tale signs that you have a date problem are as follows.

• First, some or all of the dates will be left-aligned in the cell. Date values are normally right-aligned.
• Second, you'll notice when you click on a cell containing a date that it contains a value such as '2011/05/08. That apostrophe before the date is used to indicates to Excel that the value in the cell is text value, regardless of what it looks like. This feature is sometimes useful, but not in the situation we're looking at here.

The quickest way to solve the problem, is to use the DATEVALUE function as follows:

• You have the following scenario:
  
• In a cell next to the cell you want to convert, enter this formula:
  
• Note how the formula returns a number, 39092. This is the correct answer, but it has the wrong format.
  • Excel treats all dates as serial numbers. It then uses formatting to present the serial number as a date.
  • Normally, when you enter a date Excel recognises it as a date and formats it automatically. However, Excel actually stores the serial number the date represents.
  • Clearly, this didn't happen in this example, so you'll have to format it manually.
• There are a number of ways to format a number as a date.
  • The method is to use the Number formatting dropdown list on the Home ribbon bar. You can choose from Short Date or Long Date, or you can choose the More Number Formats option and either choose a different date format or create a custom date format. We'll cover custom date formats in another lesson.
  • 
  • If you're using a Mac, the the only opNumber formatting dropdown only includes Date. To choose another date format, choose Custom...
• Once you choose a date format, the date should be presented as expected. This example shows the number in our example formatted as a date:
  
• And that's the result we needed to get to - a text value that looked like a date but didn't behave like one, converted into the correct date format.

Of course, going to all of this effort doesn't make sense when you only have a few dates that haven't converted properly. However, if you have a large spreadsheet (e.g. with hundreds of values) the DATEVALUE function can be a real time saver.

What happens when DATEVALUE doesn't work?

Sometimes, DATEVALUE just can't figure out how to convert the text value into a date. Usually it's because the text entered in the cell doesn't look like a date. Or, it could be that the text in the cell is actually a number that you know to be a date, but which Excel doesn't recognise as a date (or at least, doesn't recognise it for the date we know it to be).

• Here's an example of a text value that looks like a date but which just doesn't convert with DATEVALUE:
  • 20120111
• It's not obvious to Excel that this is a date, so DATEVALUE doesn't know what to do with it - it will just return a #VALUE error instead.

Option One - Combine DATEVALUE with the TEXT function

However, in this example we know that this is a date. We can break it out into its constituent parts - the year is 2012, the month is January, and the day is 11. Given this, we can use the TEXT function to help us out.

• The TEXT function converts a value into text. I know that sounds like the opposite of what what we're trying to do - but this method actually works very well.
• The syntax of the TEXT function is this: =TEXT(value, format_text)
  • value is what we want to convert (or a cell containing that value)
  • format_text is the format it should be converted to.
• In the example below, we're using a number that has been imported as a text value. Note that the TEXT function would also work if this had been imported as a number rather than as a text value:
  
• This is what the TEXT function does to the text value in B7:
  
• We're nearly there! The only problem we have now is that while the date looks right, it is now a text value.
• You could solve this by combining the TEXT function and the DATEVALUE functions together:
  
• The final step would then be to format the cell in B12 as a date, as we did earlier.

Option Two - A clever and obscure use of the TEXT function

Assuming the approach shown in Option One works in your particular example, Option Two will give you a better way of getting to the same answer.

• As before, we're trying to convert this value to a date:
  
• This time, we'll only use the TEXT function to get the answer we want:
  
• As above, you can now format this number as a date.
• You may well be trying to figure out how this formula manages to get the same result. Here's how:
  • When we used the two minus signs (-) in the formula, we forced Excel to use the output of the the TEXT function in a calculation.
  • To achieve this, Excel converted the text value into a number so it could complete the calculation without generating an error.
  • What we actually did was convert the text value that the TEXT function generated into a negative number, and then back into a positive number.
• As you can see, this method is a quick and easy way to get the result we wanted, and achieves the same outcome without needing the DATEVALUE function.

Option Three - Splitting the text into its constituent parts and recombining them into a date

The final method you can use when DATEVALUE doesn't deliver what you want is also the most cumbersome - but potentially the most flexible. Once you know this method, there are all sorts of uses you can put it to that have nothing to do with dates. I've already covered it in another lesson, Extract text from a cell in Excel, but it's worth showing you how to apply the method in this specific scenario.

• As before, we're trying to convert this text value to a date:
  
• You can then use the LEFT, MID and RIGHT functions to extract the year, the month and the day from the text value. From there, you can use the DATE function to recombine them into something that Excel recognises as a date.
  • The LEFT function allows you to choose a specified number of characters from the left end (i.e. the beginning) of a text value.
  • The RIGHT function does the same as LEFT but starts from the right end of the text value.
  • The MID function allows you to choose a specified number of characters from a specified starting point within the cell. In fact, you can do what we need with just the MID function, but let's look at how we might use all three.
  • The DATE function takes a Year, a Month and a Day and combines them into a date (which Excel automatically formats as a date).
• Here's what this method looks like when applied to our problem:
  
• Let's break it out so you can see how it works:
  • The LEFT function extracted the first four characters (the Year) from the cell B7 (i.e. the first four characters in the cell)
  • The MID function extracted two characters (the Month) from B7 starting from the 5th character from the left
  • The RIGHT function extracted two characthers (the Day) from B7 starting from the right (i.e. the last two characters in the cell)
  • The DATE function combined the Year, Month and Day into a date, and then Excel formatted it for us.
• Some points to consider with this solution:
  • You could have used the MID function in place of the LEFT and RIGHT functions had you wanted to. I leave that to you to figure out. I used all three functions in this example because the LEFT and RIGHT functions are sometimes very useful on their own, so I wanted you to see them in action.
  • If you have a value that includes a time as well as a date, you can use the TIME function to convert the time portion of the text into a valid time, and add the results together to get a valid date/time. There is an example of converting text to both a date and time in the comments below.
  • There are many, many scenarios where a formula that combines LEFT, RIGHT and MID will prove very handy. This has been just one of them.

A final word on working with Dates in Excel

Dates are always problematic, and Excel's treatment of them can often cause confusion. One thing I have not addressed in this lesson until now is the fact that the US uses a date format that is different to most of the rest of the world. Specifically:

• The US refers to dates in the format "month, day, year"
• The rest of the world (ROW) refers to dates in the format "day, month, year"

That means that when you're working with dates in Excel, you need to know which date format your computer is set up to use (Excel takes its lead from the computer's date settings). Otherwise you can get some strange behaviour - and errors - showing up in Excel.

This example shows how confusion can arise.

• The date 04/05/2013 in the US refers to the 5th of April, 2013.
• In the ROW, it refers to the 4th of May, 2013.
• When sharing a spreadsheet with a colleage that uses a different date format to you, Excel will automatically switch the dates around so they appear in the correct format. But if you're working with a system that, unbeknown to you, is using a different format, you can get into all sorts of trouble.

This example shows how things can get even worse:

• The date 04/14/2013 in the US refers to the 14th of April, 2013.
• For the ROW, typing this into Excel would generate an error since there are only 12 months in the year, not 14.

On a day to day basis, most Excel power users are used to this problem and normally see it coming. But even the best of us can have a moment of confusion when a formula we tried to write comes back with the wrong answer or an error, before we realise what the cause is, and move on. In practical terms, when using the methods shown in this lesson for converting text values into dates, be aware that how you construct your formulas may depend on the date format being used in your copy of Excel.