Excel. Love it or Hate it?
The date is formatted to MDY (mm/dd/yyyy). This is a format used only in the US and a few smaller countries. The first way, you can convert date is to use the Format Cells window. In order to do it, click cell B3, then click the right mouse button and select the Format Cells option. We do need some methods which we can use to convert a text to an actual date with a right format. So today, in this post, I’d like to share with you 10 methods which we can use to convert a text to date format in Excel. For converting a text to date we need to use a combination of different functions. So let’s get started. Well, Excel dates are, under the hood, just numbers. You don’t really need to know exactly what number a date is (although you can change the cell formatting to “Number” when a date is displayed and you will see the number). But, if you think about it, if you divide a date by 14, it’s going to have a remainder between 1 and 13.
It's a remarkable piece of software created by Microsoft, and to my own surprise, found out that Excel 2.0 was first released for Windows back in 1987.
That's almost 30 years ago, for those who haven't quickly opened a spreadsheet to work it out or can't do mental arithmetic.
Excel is wonderful. It really is. Get to know how to use the tools properly, or be shown by smarter people, it will genuinely save you time and stress. Most of us can create a table, a simple formula and a pie chart. However, I've decided to write a few posts and share back to the community some tricks that you might find helpful.
Seriously, if this is helpful, give it a Like and/or Share. This may be my only barometer of success for this post.
Your Excel Problem
Dates are in US format MM/DD/YY and you want them in UK format DD/MM/YY
Excel spreadsheets with US date formats are a nightmare for those living in the UK. End of.
You may have tried a number of solutions to no great success, sorting, pivoting, cut/paste, formulas, etc. Thankfully, I may have saved you from wasted minutes and hours of Excel frustration.
Excel Solution
This really is quite a simple and effective solution.
- Select the Column of dates (for example Column A in my image above)
- Under the Data tab, select 'Text to Columns' (it's a button in the middle of the toolbar as shown in my image above)
- Select the Delimited option and hit the Next button
- Untick all the Delimiters, e.g. Tab, Semicolon, Comma, Space and hit the Next button
- Select the Column Data Format 'Date: MDY' and hit the Finish button.
- Right-click the highlighted column of dates (e.g. the data you've just converted) and open Format Cells to change the Date format into the style you need. For example DD/MM/YY, DD/MM/YYYY or whichever format is relevant such as DD-MMM-YY in the Custom section.
I reckon that took you no more than 30 seconds to do.
Date formats around the World
Now you might ask, why does the US have this format?
I honestly couldn't tell you, but this graphic from The Guardian does show the data formats typically used around the globe.
Image credit: John Harding/Mona Chalabi via The Guardian
How To Convert Dates In Excel To Sort
Wikipedia have a comprehensive page on the date formats by country with plenty of resources and supporting comments. Likely more up to date that the Guardian image.
I hope you found this useful. I would like to credit Scott Williams for this hack, as it was via his website that I had discovered this simple solution. HT to you Scott.
Convert Dates In Excel From Uk To Us
About Jim Haysom
Jim Haysom is a digital leader with over 16 years’ experience creating digital marketing solutions, transforming digital businesses and growing start-up divisions.