This tip is all about that annoying time when someone gives you some dates for your spreadsheet and they come in all wrong. What has happened ?

You will be painfully aware of it if you work in a multi-regional company, and you are in Europe.

Well... most likely, along the way, someone has been using an American date format:

mm/dd/yyyy

Why on earth would you format a date like that ??? America, you have invented a lot of amazing things, but this date format really sucks.

If you receive this without knowing the original format: 01/02/2009  - Is that 1st Feb, or 2nd Jan ?

This is the source of a huge amount of problems with Excel when dealing with dates.

Sometimes they're easy to spot as 12/20/2009 will be converted to a string and hence formatted by Excel on the left. Sometimes they may not be spotted, and can cause huge issues.

Fortunately, help is at hand with a formula that can convert back to proper dates.

Here's an example... In notepad, we have the following dates:

We then copy, and paste into Excel. This is what happens:

SO... if you see that telltale sign that one of the dates is formatted to the left, you need to do the following:

Step 1: Format the column as text - Right click, format cells, number, Text

Step 2: Go back to your notepad and copy and paste the dates back over

Step 3: Use text manipulation formulae to reconstruct the date.

Step 4: Go back and give the person who gave you these dates a good hiding.

Download sheet to practise how to resolve your date problem in Excel

Training Video on how to fix your Date problem: