+ Reply to Thread
Results 1 to 17 of 17

Dates out of order...

  1. #1
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    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?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,786

    Re: Dates out of order...

    There is nothing wrong with the raw data.
    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by FDibbins View Post
    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?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,471

    Re: Dates out of order...

    Chris, are you still running XL2002. With newer versions, 2010 and later, we may be able to apply Power Query actions to ensure that the dates are all "Date" formatted. If you are running a later version of Excel, please update your profile.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,921

    Re: Dates out of order...

    You can just add a formula column using =ISNUMBER(A2) coped down, then filter that for FALSE.
    Rory
    I drink, and I know things

  6. #6
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by rorya View Post
    You can just add a formula column using =ISNUMBER(A2) coped down, then filter that for FALSE.
    Thanks for that good suggestion!
    I just did it and the only cell that was FALSE was the top cell which was the header 'Date'.
    So none of the data has a text cell.
    Last edited by ChrisXcel; 06-17-2020 at 02:22 PM.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,471

    Re: Dates out of order...

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

  8. #8
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by alansidman View Post
    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.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,786

    Re: Dates out of order...

    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.

  10. #10
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by FDibbins View Post
    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."

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,786

    Re: Dates out of order...

    Did you follow the instructions in the yellow banner above?

  12. #12
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by FDibbins View Post
    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.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,786

    Re: Dates out of order...

    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

  14. #14
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    Quote Originally Posted by FDibbins View Post
    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.

  15. #15
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,921

    Re: Dates out of order...

    Do you have access to Onedrive or Dropbox so you can post the workbook there and then share a link?

  16. #16
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002, 2013
    Posts
    262

    Re: Dates out of order...

    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.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,786

    Re: Dates out of order...

    Thanks for the update

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count dates in order (that are out of order)
    By Mogal in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-01-2018, 11:35 AM
  2. How to sort my dates in order
    By Eaglepi in forum Excel General
    Replies: 6
    Last Post: 11-07-2017, 01:40 PM
  3. Dates not in order in graph
    By ExcelError_01 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-18-2016, 09:31 AM
  4. [SOLVED] Dates To show blank FIRST then rest of dates in Descending order
    By b301 in forum Access Programming / VBA / Macros
    Replies: 15
    Last Post: 10-20-2015, 04:52 AM
  5. putting dates in order
    By union in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-22-2013, 08:17 AM
  6. Excel 2007 : Filter Dates - why not shown in order?
    By Chris001 in forum Excel General
    Replies: 0
    Last Post: 09-03-2009, 08:23 AM
  7. Dates in Order
    By Dena B in forum Excel General
    Replies: 1
    Last Post: 08-31-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1