Results 1 to 3 of 3

Thread: Pivot table not recognising date. Power pivot, normal data...
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular nikio8's Avatar
    Join Date
    Oct 2017
    Posts
    123
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot table not recognising date. Power pivot, normal data...

    Hi all,
    having this issues for quite a while a am not able to resolve it.
    When i use pivot table dates are not being recognised as dates.
    I consider myself power user and daily reconcile millions of rows of data, but this little issues is quite annoying.

    Particularly annoying is that a list several years long is sometime sorted randomly. It may be quite tricky to find particular date or to group dates for one month,
    Original data seems fine, even using power pivot to import it, and it recognises it as date. When I filter it it is correctly sorted to two or three years, months, days.
    in pivot table, whether in rows or filter area, it is just sortd as text.

    Another issue is that rows are in format such as "1 Jan 2019", and I am unable to use SUMIFS for some quick calc.

    One solution that I find is to import data using power pivot and then create calendar table and link to original data. But then it may not be very simple to filter other fields, not to mention the extra step.

    This is usually the first step, it cannot be this complicated, as later as everything will extraordinarily complicated.

    Image and video hosting by TinyPic

    Thanks in advance
    nik
    Last edited by nikio8; Jul 30th, 2019 at 06:29 AM.

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot table not recognising date. Power pivot, normal data...

    Can you post the workbook somewhere for us to look at (obfuscate the data if sensitive)?

  3. #3
    Board Regular nikio8's Avatar
    Join Date
    Oct 2017
    Posts
    123
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot table not recognising date. Power pivot, normal data...

    Quote Originally Posted by theBardd View Post
    Can you post the workbook somewhere for us to look at (obfuscate the data if sensitive)?
    This is the whole data, as per below. Note how excel wants to sort data ascending rather than earliest to latest date?
    Almost every time you put date in pivot table it does not get grouped automatically, unless it is a part of a hierarchy.
    Perhaps the reason is as the dates are not continuous group or there are blanks? If i expand the data so each date is present, it may work...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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