Converting US dates to UK format in Excel

This has bugged me for YEARS.

Take a column full of US dates in Excel that have been formatted as text and convert them to a “proper” format that can actually be used to sort by… i.e. the date format used by the rest of the world.

While annoying, this is relatively easy if all your dates are in fixed width mm/dd/yy (e.g. 03/12/14) or mm/dd/yyyy format, you can just use simple mid/left/right formulas to extract them knowing exactly where each will be.

If on the other hand the dates are in m/d/yy (e.g. 3/5/14 or 25/11/14) or m/d/yyyy format then it gets a bit messier as you now need to start finding and splitting the text at the slashes.

Today I found a really simple way to do it.

Change Word captions from Table to Figure, or Figure to Table

You’ve written this wonderfully detailed and formatted document, updated your Table of Contents and your Tables of Figures and Tables… and realised you missed the fact that all your Table were being captioned as Figures.

There are two ways I know to correct it, both are manual though and you just need to pick the one that works best for you.