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

[nikio8]

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

 

[theBardd]

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

 

[nikio8]

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...