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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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...
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top