If you import data to Excel from another program chances are the dates will come in formatted as text, which means they’re not much use to you in formulas or PivotTables.
There are many ways to fix the dates and the method you choose will depend partly on the format they’re in and partly based on your preference for a formula or non-formula solution.
Tip: You can often tell that a number or date is incorrectly formatted as text because it is aligned to the left. The default alignment for text is left and numbers and dates are aligned right.
Note: since I’m in Australia our dates are formatted dd/mm/yyyy so I’ll be using this format in the following examples.
Jump to Section
- 1. VALUE Function
- 2. DATEVALUE Function
- 3. Find & Replace
- 4. Text to Columns
- 5. VALUE & SUBSTITUTE Functions
- 6. Error Checking
1. VALUE Function
Where the ‘text’ is the reference to the cell containing your date text string.
The VALUE function was included in Excel for compatibility with other spreadsheet programs, and I'm glad it was cause it's handy.
It can convert any text string that looks like a number into a number, so it’s useful for fixing any number, not just dates.
Tip: What’s also useful about the VALUE function is it will convert a cell containing date and time that is formatted as text, as you can see in row 7 above where 2014-1-1 8:00 became 41640.3333, which is the Excel serial number for the date and time: 1/01/2014 8:00 AM, whereas the DATEVALUE function, which we’ll look at next, ignores the time portion of the text string.Notes:
- If your text string isn’t in a format recognised by Excel it will return the #VALUE! error.
- Excel will return the serial number for your date, you will then need to format the cell with a date format to make the serial number ‘look’ like a date as I have done in column E above.
2. DATEVALUE Function
Where ‘date_text’ is the reference to the cell containing the date text string.
DATEVALUE is very similar to the VALUE function except it only converts a text string that looks like a date to a date serial number. It can’t handle any old text string that happens to look like a number, for that you need the VALUE function.
- Time information in date_text is ignored, as you can see in row 13 above where the result is the date portion only.
- If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer's built-in clock as you can see in row 15 above.
- Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Or, in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.
3. Find & Replace
If your dates have decimal places as delimeters then VALUE and DATEVALUE are of no use to you, for example dates like this:
This is where Find & Replace is a great option.
Using Find and Replace to replace the decimal with a forward slash converts the text string to an Excel serial number in one fell swoop.To use Find & Replace:
- Select all the dates you want to fix
- Press CTRL+H to open the Find & Replace dialog box<
- Enter a decimal place in the ‘Find what’ field, and a forward slash in the ‘Replace with’ field
- Click ‘Replace All’:
Excel should detect that your text is now a number and format it automatically as a date. If that doesn’t work, you can try the next tool; Text to Columns.
Tip: You can also use Find & Replace to fix date text strings with other delimiters like spaces, or the hyphens we saw in the VALUE and DATEVALUE examples. Just enter a space or hyphen in the ‘Find what’ field instead of the decimal place.
4. Text to Columns
Personally I love Text to Columns. It is one of the most versatile tools for fixing data imported from other systems. I’m going to show you how to fix dates with it now, but I recommend you take some time to play around with the other options it offers.
So, if your dates are formatted in text strings like this:
You can use Text to Columns to quickly reformat them all.
Simply select your dates in column A > Data tab of the ribbon > Text to Columns to open the Wizard:
Step 1 of the Wizard: Choose ‘Delimited’
Step 2 of the Wizard: uncheck all Delimiters (just to be safe)
Step 3 of the Wizard: Choose 'Date' from the 'Column Data Format' options and choose your date format from the drop down list (my dates are YMD), and click the Finish button:
Tip: Note how the ‘Date’ drop down list above has many different combinations of D M Y, you simply choose what order your dates are formatted in from the list.
This means you could have also used Text to Columns to fix the dates we looked at with VALUE and DATEVALUE that had hyphens, or even decimal place delimiters, or for example if your dates are text strings like these:
Jan 1 2014 Jan 2 2014 etc.
You’d simply choose MDY from the drop down list in step 3 of the wizard to fix the dates above. Versatile eh?
Now, while Text to Columns is pretty powerful, it has its limits. For example if your dates are formatted like this:
Wednesday, January, 1, 2014
You need to put in a bit more effort. Here is a tutorial where I use both Text to Columns and the DATE function to fix the above date format.
VALUE and SUBSTITUTE Functions
If you prefer a formula solution to the Find & Replace or Text to Columns options above, then you can use the VALUE function with SUBSTITUTE on stubborn dates that use delimiters like decimals:
The syntax for SUBSTITUTE is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
So, you can see in the above examples we are using SUBSTITUTE as our ‘text’ argument in the VALUE function to replace the decimals with forward slashes, just like we did with Find & Replace.
The VALUE function then converts the output of SUBSTITUTE to an Excel serial number.
Tip: We have omitted the ‘instance_num’ argument in SUBSTITUTE as we want to replace all of them.
Now, Excel is pretty clever (and I’m not just saying that because I love it 😉 ); it has inbuilt error checking that is always on the lookout for numbers formatted as text.
You can tell if it has spotted an error because it will have a small green triangle in the top left of the cell and if you select the cell an exclamation mark will appear:
Clicking on the exclamation mark will reveal some options relevant to your text:
In this case the dates above only have a 2-digit year so Excel is asking me what format I want to convert it to; 19XX or 20XX.
You can quickly fix all dates using the error checking by selecting all of your cells containing date text strings before clicking on the Exclamation mark on the first selected cell.
Turn on Error Checking
To make sure Error Checking is turned on go to Excel Options > Formulas > and make sure the following options in orange are checked:
So, there you have 6 different ways to convert dates formatted as text in Excel. There are many more ways with formulas, but the above should allow you to handle almost any permutation quickly and easily.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.