Date format in Pivot table not changing

GTretick

Member
Joined
Jul 29, 2015
Messages
42
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
365
I've created an example Pivot table that has a few categories in a table that feeds into a Pivot Table.

The date formatting in the Table is set to mmm d, yyyy and in fact appears that way.

The pivot table shows the formatting as mmm d, yyyy but instead appears as d-mmm.

I've tried playing around to change it but nothing seems to change the formatting.

Is it possible to change the formatting or am I stuck?

If so, what are the steps?

Thanks.
 

Attachments

  • Example Pivot Table.xlsx
    17.4 KB · Views: 12
Does this presentation work for you? On the analyze tab, you will need to adjust your grouping.

Excel 2016 (Windows) 32 bit
A
B
1
Row LabelsSum of Count
2
Ellen
442
3
Canada
148
4
2020
148​
5
Mar
54
6
1-Mar
16​
7
15-Mar
38​
8
Apr
94
9
1-Apr
12​
10
15-Apr
82​
11
India
139
12
2020
139​
13
Mar
120
14
1-Mar
28​
15
15-Mar
92​
16
Apr
19
17
1-Apr
14​
18
15-Apr
5​
19
United States
155
20
2020
155​
21
Mar
65
22
1-Mar
58​
23
15-Mar
7​
24
Apr
90
25
1-Apr
63​
26
15-Apr
27​
27
Mark
635
28
Canada
215
29
2020
215​
30
Mar
58
31
1-Mar
42​
32
15-Mar
16​
33
Apr
157
34
1-Apr
57​
35
15-Apr
100​
36
India
210
37
2020
210​
38
Mar
63
39
1-Mar
37​
40
15-Mar
26​
41
Apr
147
42
1-Apr
35​
43
15-Apr
112​
44
United States
210
45
2020
210​
46
Mar
100
47
1-Mar
86​
48
15-Mar
14​
49
Apr
110
50
1-Apr
21​
51
15-Apr
89​
52
Grand Total
1077
Sheet: Report
 

Attachments

  • Example Pivot Table.xlsx
    24.6 KB · Views: 7
If you prefer this presentation, then just ungroup all dates in the analyze tab

Excel 2016 (Windows) 32 bit
A
B
1
Row LabelsSum of Count
2
Ellen
442
3
Canada
148
4
1-Mar-2020
16​
5
15-Mar-2020
38​
6
1-Apr-2020
12​
7
15-Apr-2020
82​
8
India
139
9
1-Mar-2020
28​
10
15-Mar-2020
92​
11
1-Apr-2020
14​
12
15-Apr-2020
5​
13
United States
155
14
1-Mar-2020
58​
15
15-Mar-2020
7​
16
1-Apr-2020
63​
17
15-Apr-2020
27​
18
Mark
635
19
Canada
215
20
1-Mar-2020
42​
21
15-Mar-2020
16​
22
1-Apr-2020
57​
23
15-Apr-2020
100​
24
India
210
25
1-Mar-2020
37​
26
15-Mar-2020
26​
27
1-Apr-2020
35​
28
15-Apr-2020
112​
29
United States
210
30
1-Mar-2020
86​
31
15-Mar-2020
14​
32
1-Apr-2020
21​
33
15-Apr-2020
89​
34
Grand Total
1077
Sheet: Report
 
The presentations above are not really what I'm looking for.

the dates are still not in a mmm d, yyyy format.

I see in the second example the year is added to the formatting but the day and month are still mixed.

While dates are my main focus, ulitimately it's about how do you change formatting in various label row groups.
 
Excel 2016 (Windows) 32 bit
A
B
1
Row LabelsSum of Count
2
Ellen
442
3
Canada
148
4
Mar 1, 2020
16​
5
Mar 15, 2020
38​
6
Apr 1, 2020
12​
7
Apr 15, 2020
82​
8
India
139
9
Mar 1, 2020
28​
10
Mar 15, 2020
92​
11
Apr 1, 2020
14​
12
Apr 15, 2020
5​
13
United States
155
14
Mar 1, 2020
58​
15
Mar 15, 2020
7​
16
Apr 1, 2020
63​
17
Apr 15, 2020
27​
18
Mark
635
19
Canada
215
20
Mar 1, 2020
42​
21
Mar 15, 2020
16​
22
Apr 1, 2020
57​
23
Apr 15, 2020
100​
24
India
210
25
Mar 1, 2020
37​
26
Mar 15, 2020
26​
27
Apr 1, 2020
35​
28
Apr 15, 2020
112​
29
United States
210
30
Mar 1, 2020
86​
31
Mar 15, 2020
14​
32
Apr 1, 2020
21​
33
Apr 15, 2020
89​
34
Grand Total
1077
Sheet: Report

Highlight the date column in your PT and then set the format in Custom Formats.
 

Attachments

  • Example Pivot Table.xlsx
    22 KB · Views: 4
Back
Top