Learn Excel Date Re-Formatting in Excel Tip

Date Re-Formatting in Excel Tip

No matter your job, date formatting can be a huge pain in the arse.

Sure, a lot of spreadsheet solutions try and auto detect this, but when they don’t it can lead to massive errors and waste a lot of your time.

For example if you use the mm/dd/yyyy format, but you get a file with dd/mm/yyyy, the 1 to 12 January turns into the first of each other month, and 19 other values that won’t be recognized as dates because there is no 13th month and beyond:

Does this:

When you want it to do this:

But if this does happen, did you know you can fix it in 5 seconds with a simple function in Excel? It’s buried in the Text to Columns menu. To find it, follow these quick steps:

  1. Simply select the cells you want to convert
  2. Then click Data in the top menu
  3. Text to columns
  4. Click next
  5. Then untick all of the boxes in the delimiter section that would turn this into multiple columns, and click Next
  6. This is the section you want. In the dropdown, choose the date format you are currently seeing, not what you want (in this case it would be DMY)
  7. Click finish

And voila, Excel has now magically converted the cell values into recognizable computer dates (and has probably reformatted them into your systems default format).

Easy right? So next time you encounter this, there’s no need to use complex equations to fix it. Just remember this simple trick and you’ll fix things in seconds!

Check out this data viz below to see if you’re in the minority or majority of using your date format, and who your format friends are.


Please enter your comment!
Please enter your name here


Tableau PublicYouTubeTwitterFacebookLinkedInInstagramEmail Newsletter

Latest Articles

Groceries made easy using Google Sheets

Have you ever wanted to help out more around the house, but not have it take up a lot...

Time Saver Tool v2.0

Have you ever discovered a way to speed up a process but cannot convince others to do it? Felt like you don't...

SG Tableau User Group Oct 2020

The SG Tableau User Group just wrapped up it's October virtual livestream event, which you can now watch on YouTube here. It...

Find insights beyond the summary stats (w/ data viz)

In this video we will see how summary stats can be misleading and the importance of data viz to easily dig deeper into the numbers beneath them.

Data Studio Community Livestream | Jun 2020

Welcome to the first Data Studio Community Livestream - you'll see 4 speakers covering a wide variety of topics, which should help any and...