Results 1 to 6 of 6

Thread: Date format in Pivot table not changing

  1. #1
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365

    Date format in Pivot table not changing



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    272
    Articles
    0
    Excel Version
    2019
    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 Labels Sum 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
    Attached Files Attached Files

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    272
    Articles
    0
    Excel Version
    2019
    If you prefer this presentation, then just ungroup all dates in the analyze tab

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Row Labels Sum 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

  4. #4
    Acolyte GTretick's Avatar
    Join Date
    Jul 2015
    Location
    Canada
    Posts
    33
    Articles
    0
    Excel Version
    365
    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.

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    272
    Articles
    0
    Excel Version
    2019
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Row Labels Sum 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.
    Attached Files Attached Files

  6. #6
    Neophyte spillerbd's Avatar
    Join Date
    Apr 2016
    Location
    Collierville, TN
    Posts
    3
    Articles
    0
    Excel Version
    Office365, Office 2016
    Have you Ungrouped the dates in the Pivot Table?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •