66. Excel Tips - American date to proper date.

Nick's picture


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:

date-problem-Excel

 

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

date-problem-Excel

 

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.

date-problem-Excel

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

Training Video on how to fix your Date problem:

AttachmentSize
date-problem.xls20 KB

American to 'proper' date

Ha!