Date format and order in Slicer

eggem01
NewLounger
Posts: 16
Joined: 21 Jun 2019, 07:11

Date format and order in Slicer

Post by eggem01 »

I have an Excel-table with Date, Month and Year and some columns with data.
Every column has his own pivot table.
Finally I want to show the results of all in a small Dashboard. I also used some VBA for a bit more flexibility.
Everything works just fine except the formatting and order in the Date slicer.

- I don't understand why the format of the date in the PT is d-mm while the setting is dd-mm-yyyY.
- If I look at the PT-fields and more specific the Datum-field, I also see dates from earlier months. Why is that.
- Why is the Slicer Datum order alphabetically instead of chronological.
- Strange too is that when i use the normal formula for extracting the month out of a date (=Month(..) I get January instead of April.

I have worked with this many times, but here I have done something wrong. Just can't understand what.
Probably something simple, but I have looked at it too long to be able to solve this any longer.

Marcel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71516
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date format and order in Slicer

Post by HansV »

- I don't understand why the format of the date in the PT is d-mm while the setting is dd-mm-yyyY.

I removed all row fields from the first pivot table.
Then I added Datum back, and it was displayed as dd-mm-yyyy in all pivot tables.
It remained that way when I added Jaar and Maand back.

- If I look at the PT-fields and more specific the Datum-field, I also see dates from earlier months. Why is that.

I see the same dates in the pivot tables as in your source data - althoough most dates are in April and May of this year, you also have a date in July of 2020 and a date in June of this year.

- Why is the Slicer Datum order alphabetically instead of chronological.

Sadly, slicer entries are always sorted as text. There is no way to change this, except by changing the format of the Datum column to yyyy-mm-dd and refreshing the pivot tables. Or perhaps you'd even need to change the system date format to yyyy-mm-dd.

- Strange too is that when i use the normal formula for extracting the month out of a date (=Month(..) I get January instead of April.

That is because MONTH returns the month number. For April, this is 4. But if you format this as mmmm, it treats 4 as a date: January 4, 1900. You were correct to use TEXT instead.

Dashboard Rapportage Statussen.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

eggem01
NewLounger
Posts: 16
Joined: 21 Jun 2019, 07:11

Re: Date format and order in Slicer

Post by eggem01 »

Thanks Hans.

p45cal
StarLounger
Posts: 51
Joined: 11 Jun 2012, 20:37

Re: Date format and order in Slicer

Post by p45cal »

I think your file has been created in Excel 2013, so you should have Timelines available to you, and you should be able to group dates in months and years in the pivot table, then you wouldn't need your month names and years as separate fields at all.
In the attached i've copied the first pivot table on th DT's sheet below it to cell B49, where I've only used the Datum field for the dates. I've also added Timelines (I didn't have to add three, one would have done) instead of slicers. Now the months are in chronological order.

More info here:
10 steps to adding a timeline to an Excel 2013 PivotTable - TechRepublic
https://www.techrepublic.com/blog/10-th ... ivottable/
Timeline in Excel - How to Create? (Steps by Step Example)
https://www.wallstreetmojo.com/timeline-in-excel/
You do not have the required permissions to view the files attached to this post.