#1
|
|||
|
|||
"Cannot Group That Selection" issue with dates in Excel Pivot Table
I am getting the above error when I try and group dates in a pivot table (I want to have "Year" as a filter in the header)
I attach the list of dates and if you open the filter and scroll to the bottom, you will see that a number of dates are shown separately. I cannot see what is wrong, as they are formatted as Date. |
#2
|
||||
|
||||
It's less how they're formatted more the actual contents; in some rows you have text, not real Excel dates. Below, those left justified are strings, those right justified are good:
2023-06-28_110357.jpg |
#3
|
|||
|
|||
Thanks for that analysis p45cal.
Can you explain to me how you were able to visually separate the two, so that I can correct. Also, this is downloaded data from my bank. It is downloaded as a .csv and I do some editing and formatting before pasting into a master spreadsheet (.xlsx) I may be inadvertently causing this problem myself. The downloaded date comes in format dd-mm-yyyy. I use Find/Replace to change "-" to "/", and then format the cell as Date (dd/mm/yyyy). This is done in the .csv before pasting to the master spreadsheet. Is this the cause? If so, what would be your advice for formatting of the date column in the download? Tommy |
#4
|
||||
|
||||
Quote:
Remove any left/right justification from that column. Numbers will be ranged right, text ranged left. Quote:
Use Power Query (Get & Transform Data) to import the data, it doesn't try to interpret the dates and you'll be able to do the various transformations including conversion to proper dates Quote:
If you want, send me in a Private Message here (I suspect you don't want the data in the public domain) the actual untouched csv and I'll write (actually, no writing, just clicking, to create) a bit of Power Query code to handle the import of future csvs. |
#5
|
|||
|
|||
Thanks p45cal.
I've managed to identify the errant cells by clearing formatting The download is from Portugal and is being used in Dublin, so both in ddmmyyyy format I've never used PowerQuery, but would be keen to work with you to automate it if that's ok. I do the editing directly in the .csv file. The only amendments I make are to remove cell merging (in several rows) where this exists and also I may delete some rows before running the "Replace" activity and then copy/paste to master sheet in .xlsx Appreciate the offer to work with you further in DM. I will send to you now. Thanks for the support. Tommy |
#6
|
|||
|
|||
Presuming it was simply a "number stored as text" issue, I tried refreshing the cells. Didn't change those few problem ones towards the bottom.
I selected column A and changed the date format to d-mmm-yyyy. All but that handful changed displayed formats accordingly. I then added a column to the right and typed in some dates. When I (American) attempted to input the dates with the European date order (23/03/2023) it just went in as typed, didn't pick up the formatting I'd applied. When I typed it in in American order (3/23/23) it displayed as "23-Mar-2023." It leads me to believe that the items that aren't looking right are coming from a system that doesn't default to the same date format the rest of the data has. Different separators, perhaps, or an American format? There is a formula you can use to essentially flip the date and month digits in Excel without using Power Query; it seems to work on those left-justifying figures, but makes the other ones go completely wrong. It's Code:
=DATE(VALUE(RIGHT(C2,4)), VALUE(MID(C2,4,2)), VALUE(LEFT(C2,2))) |
#7
|
|||
|
|||
Thank you kilroyscarnival
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro | Matt C | Excel Programming | 2 | 01-08-2022 06:03 AM |
Irritating formatting issue in Table of Contents - page number "dots" disappear | Eriks | Word | 9 | 04-19-2019 06:18 PM |
Pivot table dates issue | salse | Excel | 1 | 06-08-2016 12:23 PM |
Excel Macro to "Select All" in Pivot Table Field | galkej | Excel | 0 | 02-03-2014 11:59 AM |
Excel pivot table into an MS Word Doc "office 2007" | wmarsh3561 | Excel | 1 | 01-09-2010 08:03 PM |