I always forget how to convert simple text strings into dates in Excel

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.

Screen Shot 2017-03-25 at 2.34.20 PM

Select the text you want to convert and then go to ‘Data>Text to Columns’

Screen Shot 2017-03-25 at 2.36.22 PM

A dialog box will open. Just keep the selection as ‘Delimited’ and click ‘Next.

Screen Shot 2017-03-25 at 2.36.58 PM

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’.

Screen Shot 2017-03-25 at 2.38.55 PM

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’.

Screen Shot 2017-03-25 at 2.41.31 PM

This puts each number from the text dates into their own column.

Screen Shot 2017-03-25 at 2.44.03 PM

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)

Screen Shot 2017-03-25 at 3.46.03 PM

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’.

Screen Shot 2017-03-25 at 4.03.24 PM

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)’.

Screen Shot 2017-03-25 at 4.04.35 PM

I then redo the ‘Paste Special’ process on the ‘Value Equation’ column to move the values into a column that is formatted as ‘Date’.

Screen Shot 2017-03-25 at 4.07.01 PM

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.

The oldest Chinatown in Europe is in Amsterdam

I started this blog in 2006 as a way to share my travels and perspectives with family and friends. Since then I’ve written over two hundred posts and there are always ones that are still a work-in-progress. Last week, I decided to do a bit of spring cleaning and found some tips I had collected for a 2012 trip to Amsterdam. So if you’re planning to travel to the city infamous for pot (i.e. marijuana) and prostitution (i.e. legalized), these other sights might be interesting if you’re looking for something more.

P1090016

Like science? There’s a green, shiplike building on the eastern harbour designed by big-name architect Renzo Piano that houses NEMO. The science museum has loads of interactive exhibits to entertain kids, such as drawing with a laser, ‘antigravity’ trick mirrors, and a ‘lab’ where you can answer questions such as ‘How black is black?’ and ‘How do you make cheese?’ One of the best places to chill out and take in the view of Amsterdam is NEMO’s stepped roof (admission free) – it’s worth a stair climb for its fantastic views.

P1090003

The Anne Frank House is a compact museum with a layout that requires visitors to keep moving at a steady pace. Visitors should allow about one hour to complete the tour, excluding time to wait in line which could be quite long given the history and reputation of the place. And if you’re not in shape, beware that there are a lot of steps to climb moving from floor to floor.

P1090023

Amsterdam has mainland Europe’s oldest Chinatown. It was weird to pass over the canals of the city and then suddenly find ourselves surrounded restaurants and shops offering food and products from all over Asia.

Sunset looking towards the main train station - can you see the Chinese restaurant/hotel?

We passed through the Red Light District one night but didn’t realize we could’ve organized a tour with the the Prostitution Information Center! If you’re tired of it all, just follow the waterways to get out of the district and walk around the harbour especially at sunset for a much better view.

P1080947

Forget about the hotels and try all the bed and breakfast options that dot the city. We stayed at “Sleep With Me“. Not only was it a quaint place run by a French and Dutch couple, it was also outside of the city centre where life is quieter and where there are plenty of things to discover on foot.

If you have other sights to share, leave me a comment.