+ Reply to Thread
Results 1 to 7 of 7

Dates are not sorted according to the source

  1. #1
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Dates are not sorted according to the source

    Hi,

    I have a table where main sorting is done based on the order date (Sample file attached).

    A pivot report is generated based on that table & I was maintaining this since March, 2019. But specifically after 1st January, 2020, the date field is not sorted according to the source order or chronologically.

    I have tried different solutions searching online, still can't get it done.

    Help is appreciated.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,353

    Re: Dates are not sorted according to the source

    The 'dates' on the summary sheet are just text, not real dates - set their formatting to general and you will see that they do not convert to a serial number, as they would if they really were dates. The custom formatting used will NOT turn text into a date.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dates are not sorted according to the source

    in spare column place formula
    it should convert text string to date and place as last year.
    copy the serial number back to your date column as a value and format appropriately.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Dates are not sorted according to the source

    Quote Originally Posted by AliGW View Post
    The 'dates' on the summary sheet are just text, not real dates - set their formatting to general and you will see that they do not convert to a serial number, as they would if they really were dates. The custom formatting used will NOT turn text into a date.
    I got your point. But the source is well formatted as Date. So, what should I do to solve it?
    Any suggestion pls?

  5. #5
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Dates are not sorted according to the source

    Quote Originally Posted by torachan View Post
    in spare column place formula
    it should convert text string to date and place as last year.
    copy the serial number back to your date column as a value and format appropriately.

    Please Login or Register  to view this content.
    I could not understand how to do it. Can you please do it in the Sample Book and upload?

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dates are not sorted according to the source

    column B with formula
    column C copy/paste values of column B format dd/mm/yyyy
    column D copy/paste values of column B sort ascending order format dd/mm/yyyy
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Dates are not sorted according to the source

    Thank you all.
    My problem is solved after formatting the source dates and pivot table dates again.

+ 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. Create sorted list based on longer, unsorted source
    By kammend in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-20-2018, 07:42 PM
  2. Links dont update when source data is sorted
    By Stuwoolf in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 03-28-2017, 07:32 AM
  3. Replies: 6
    Last Post: 10-23-2015, 12:54 AM
  4. Looking up by range of dates, returning sorted data
    By sactyr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2013, 12:23 AM
  5. Sorting or identifying dates within a cell to be sorted
    By sljgoal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2013, 05:52 PM
  6. Dates sorted
    By asdzxc in forum Excel General
    Replies: 3
    Last Post: 05-22-2012, 08:52 AM
  7. Replies: 3
    Last Post: 01-23-2006, 05:25 PM

Tags for this Thread

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