Introduction
I think everyone knows that dates in Excel can be an absolute nightmare to deal with at times. I was a victim of such a thing a couple of weeks ago. The problem is often how we, individually, format our dates and then where my dates are coming from.
I use the UK format for all of my work. The USA format is extremely common, of course. Then there are many other variations from all over the world.
How Excel deals with dates is sometimes not so transparent.
The Date Problem
I downloaded some share price data from morningstar.com: something I have been doing for several years. Normally, there are no problems with dates when I download such data but over the last six months or so, things started to get niggly. I didn't know why but I didn't explore it.
Then, since I was using share price information in July 2020 and creating a Pivot Table from it, I noticed that it was giving me share price data from history and then for July 2020, August 2020 ... December 2020. How was that possible.
Digging Deeper
As I dug deeper, I saw this:
| Date | Formatted as |
| 12/11/2019 | General |
| 13/11/2019 | General |
| 14/11/2019 | General |
| 15/11/2019 | General |
| 16/11/2019 | General |
| 17/11/2019 | General |
| 6/12/2019 | Date |
| 7/12/2019 | Date |
| 8/12/2019 | Date |
| 9/12/2019 | Date |
Notice the problem: some dates were formatted as General and other dates were formatted as Dates.
Changing the Formatting: including Power Query
I have to confess, I tried everything I knew about reformatting these dates, including using Power Query. Power Query is normally brilliant at sorting out both the simple and the complex date problems.
At one stage, in Power Query, I thought I had the answer until I tested the result by applying MONTH() to it and it failed.
Whatever I did, failed. So I took the problem to a discussion list where there are hundreds of knowledgeable Excel men and women and we had a very useful discussion about dates ... formulas ... Locale ... VBA.
In the end, this was the solution.
The Solutions
Solution One
I have faced this problem many times over the last year or so and I will start this solution section with this option.
Just accept your file as it is. That is, the second, more involved solution I am offering, might not be needed.
Since we are talking about Power Query here, create your Query that includes the date column and then do this:
File ... Options and Settings ... Query Options
Now click Regional Settings
Change the Locale to the locale of the system you are currently using
Click OK
That solved this problem for me on this and other files INSTANTLY
Solution Two
By the way, this is the solution to THIS problem and it MIGHT be the solution to your date problem ... but I cannot guarantee it!
I copied the column entitled Dates to a new, empty, file
I sorted this column A to Z and then physically split the table according to whether the results contained General formatting or Date formatting.
I converted both lists to Excel tables
In Excel, I used Flash Fill to convert from US style to UK style dates for both of the tables I now had. This gave me two tables each with two columns
I created queries of the two tables and removed the initial column, to leave me with the new, UK style, column in both cases: checking to make sure they were formatted as dates. I closed and loaded the Queries to connection only.
Finally, to rejoin the two sets of dates, I Appended Queries as New to give me the full list of all dates, now all in UK style. I Closed and Loaded my new Query to a worksheeet
I sorted this Query according to the original order in the file that I started with and can confirm that I achieved what I wanted to achieve.
In the end, smart as Excel and Power Query are, there are times when they can conspire to be a right royal PITA.
Download: there is no file to download since such problems are unique!
Duncan Williamson
22nd August 2020
No comments:
Post a Comment