Log In
Register
Home
New Member
May 12, 2020
Offline
Dear Mynda,
My question is on an excel file I downloaded from one of your webinars on
https://www.myonlinetraininghu.....-downloads
The source document has wrong date formats that only recognises the DD as the month(MM) and recognises the month (MM) as the DD when grouped. For the entire data of 22908 rows with complete months from Jan - December, my Pivot could only covert to Jan and Quarter 1 when grouped in month, quarter and year, I have tried everything possible from text to columns to custom formating, nothing seems to work.
The file is attached for your perusal.
July 16, 2010
Offline
Hi Ngozi,
Welcome to our forum!
The data isn't in the wrong format. It's just that your PC uses mm/dd/yyyy and mine uses dd/mm/yyyy. This is a common issue and one you can fix using Power Query to 'change type' > using locale > choose 'Date' and 'English (Australia)'. Note: you should remove the first 'Changed Type' step in the query before using 'Change type' > using locale.
Hope that points you in the right direction.
Mynda
1 Guest(s)
