Despite having the world’s knowledge at my finger tips (i.e. the internet), I always forget little tips on how to do things in Excel. Yes, there’s bookmarks and other different ways to save websites, but I can never find them again after I saved them! So maybe writing a blog about it might be a good way to remember and also to help other people out there who come across this same problem.
This is one of the most annoying things when working with text and numbers in Excel… getting text to be in a “date” format. It makes life a whole lot easier when running queries or trying to sort/filter/order numerical values rather than text. While there are a lot of ways to do it (see this link for example), the following method is the most straight-forward way for me to convert text into a date/number and it works every time.
So here goes…
You come across dates as a string of text and they look like this:
- 1.1.2015
- 1.2015
- 01 01 2015
- 2015/1/1
In my example, the dates come out as dd/mm/yyyy format but it really is text.
Select the text you want to convert and then go to ‘Data>Text to Columns’
A dialog box will open. Just keep the selection as ‘Delimited’ and click ‘Next.
On the next dialog box, select which ever delimiter is what separates the day, month and year for your text dates. In my example, “/” separates my text so I selected ‘Other’ and then inputted ‘/’. This puts each number into its own column. Click ‘Next’.
In the final dialog box, just keep everything the way it is and change the destination to where you want the new columns to be added. Click ‘Finish’.
This puts each number from the text dates into their own column.
Now I need to combine the numbers into the format that I want (yyyy-mm-dd). This requires a simple equation to merge the values in each column based on the order of the values I want.
=(D2&”-“&C2&”-“&B2)
Once the numbers are merged, I need to copy the values (not the equations) into a new column in the “date” format. Under ‘Edit’, select ‘Paste Special’ and then choose ‘Values’. Click ‘Ok’.
While the converted value is a number, in order to turn it into dates that Excel can recognize, I need to run a simple equation using ‘=Value (cell number)’.
I then redo the ‘Paste Special’ process on the ‘Value Equation’ column to move the values into a column that is formatted as ‘Date’.
There’s probably a quicker way to do this, but this is the best way I’ve found that provides the most control on addressing the number rather than fiddling with Excel’s functions to format columns… and it works every time.