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

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
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.

<a href="http://tinypic.com?ref=119xxna" target="_blank"><img src="http://i65.tinypic.com/119xxna.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

Thanks in advance
nik
 
Last edited:

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Can you post the workbook somewhere for us to look at (obfuscate the data if sensitive)?
 

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
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...
 

Forum statistics

Threads
1,085,094
Messages
5,381,677
Members
401,750
Latest member
J1SBikes

Some videos you may like

This Week's Hot Topics

Top