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:
- Simply select the cells you want to convert
- Then click Data in the top menu
- Text to columns
- Click next
- Then untick all of the boxes in the delimiter section that would turn this into multiple columns, and click Next
- 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)
- 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.
