Dates out of order...

What is wrong with Excel?
I keep having really quirky things happen with my tables/macros.

This one involves a pivot table.
It's gathering data and getting it ready to be graphed.

There is nothing wrong with the raw data.
The dates are all sorted correctly.

However when I look at the pivot data graph, and open up the drop-down
menu for 'Date', at the very bottom of the list this is what I see for June:

June 10
June 11
June 12
June 15
June 14
June 13
June 16

How is that possible?
What could be causing this?

Originally Posted by FDibbins

Are you sure? More often than not, when we see this kind of problem, it is because the dates are really text looking like dates, and not really dates.
Do a quick test on 1 of them...=isnumber(cell-ref) FALSE indicates text

If you are only seeing a month name and date (and no year), then that kinda confirms that you have text dates.
Where is this data coming from?

If you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like, we can confirm my suspicion

The problem is that there are over 23,000 rows of data with dates.
It would be hard to check each cell for a FALSE (text) reading.
Now granted a much smaller number of rows would contain the problem dates.
But we are still talking about finding dozens that are buried amongst 23,000 rows.

Of all the dates I tested so far all came back TRUE.
Is it common for rogue dates (text) to be inserted amongst 23,000+ other dates?

Originally Posted by alansidman

Chris -- did you read post #2 or just skip over it.

Hi Alan.
Yes I did read it (#2).
I haven't uploaded anything yet as that would be tricky.
I'd need to do a bunch of edits first.

Originally Posted by FDibbins

Just make a copy of the file, then delete everything not related to your question - and we only need a few dozen rows of data, too.

OK here it is.

Up to June 7 it's OK.
It does dates indiv. then total
So Dates for June 7, then Total for June 7

Then...
Jumps to dates indiv for June 9 and Total Jun 9 !!!
Then goes to dates indiv for June 8 and Total Jun 8 !!!
Then goes to dates indiv for June 10 and Total Jun 10 !!!
Then goes to dates indiv for June 11 and Total Jun 11 !!!
Then goes to dates indiv for June 12 and Total Jun 12 !!!
Then goes to dates indiv for June 15 and Total Jun 15 !!!
Then goes to dates indiv for June 14 and Total Jun 14 !!!
Then goes to dates indiv for June 13 and Total Jun 13 !!!
Then goes to dates indiv for June 16 and Total Jun 16 !!!

Strangely all the data before this is correct.

I know the PivotTable hasn't been set up correctly to begin with.
It seemed to do the job so I didn't try anything else.
Don't really know what to do.
Bear in mind this was created with Excel 2002!

Chris

Hmmmmmm...
I guess it will have to wait until another time.
This is the message I am getting:
"Upload of file failed."

Originally Posted by FDibbins

Did you follow the instructions in the yellow banner above?

Thanks for helping!

Yes I just did. The problem was probably because my file size was too big.

Here's the thing.
I'm having my problem with my data file. I know it's large.
The problem is not because the file size is big.
There is something else causing the Pivot Table to fail.

Now I have pared down the file to 1/3 its size and it is still is too big (2M) to upload.
If the data isn't in the file, how can you possibly see what the problem is?

I can keep just 20 rows but how the heck would you be able to find the problem then?

Here's what the Pivot Table does.
It searches through 23,000 rows of data, first looking for all countries on a second list
starting at Date1. Then it gets those totals.

Then it moves to the next Date2 finding all countries on the second list and gets those total.
etc. etc.

If I give you just 20 rows of data that wouldn't even cover one country!

There are over 50 countries on the second list.

Originally Posted by FDibbins

According to a link I found, you can have 32500 unique entries in a PT, so thats not the problem...
https://link.springer.com/chapter/10...-4302-0141-0_9

Yes you are right that's not the problem.
I have only approx. 16,000 entries in my PT.

But I was just saying that even when I pared down the data that my PT is based upon,
the file was still around 2M and it seems that this size is too big to be uploaded here.

The dilemma is, how can I upload my file so you can see what the problem is
if the site doesn't allow files that size?

If I pare the data down to just 20 rows of data, the PT will likely be meaningless then.

I finally got it to work.

I had to create a new PT from scratch.

Somehow some of the PT data/info got corrupted.

The dates are all back in order now.