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.

Attached Files

Pivot Table Date Issue.zip (23.8 KB, 2 views)

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

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

Remove any left/right justification from that column. Numbers will be ranged right, text ranged left.


Unfortunately, when you open a .csv file with dates in Excel (the same applies to pasting data), Excel tries to be helpful and converts what it thinks are dates into proper Excel dates. There are problems with this; it makes assumptions about the order dmy/mdy and those assumptions can often be wrong - it depends on your locale and the locale that the csv was created in. Some dates, eg. 2/1/2023 could be interpreted 2nd Jan, or 1st Feb, one of them is wrong. If the csv file has been created in a dmy system and is loaded into a dmy Excel there should be no problem. I believe the csv file was created in a dmy format and is being loaded into a dmy system (Dublin?) so it's a bit surprising that some are failing to be converted to dates properly. In this case I'd look more closely at the csv file using other means (Notepad) to see what could be going wrong (are there some extra spaces lurking?).


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



How do you do this? In what application?


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.

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

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

and you could then paste those values back. But, agreed, if you are dealing with bulk and frequent data, a PowerQuery solution may be best.

Thank you kilroyscarnival