Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting dates in pivot table (2003)

    I've got an Excel table for keeping track of time working on various tasks. It's got four colums: DATE; CASE NO. TIME; TASK. No big deal. I then have a pivot table that organizes the data where the rows are CASE NOs. and the columns are DATES, and the table is filled in with SUM OF TIME. I've yarked up a very small, fake table and attached it so you can see.

    Here's the problem: In the pivot table, right now the day of week and date are all on one line at the top of each column: e.g., "Sun 3/2". I want them on two lines, with, e.g., "Sun" just above "3/2", etc. Like:

    Sun
    3/2

    I want to do this to minimize the column widths. I tried to find a code to insert a line break between the day of week portion of the date code and the date itself, but I can'f find one. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting dates in pivot table (2003)

    In the attached version, I have added a calculated column Date2 in the Time sheet with formula (in row 2)

    =TEXT(A2,"ddd")&CHAR(10)&TEXT(A2,"d/m")

    CHAR(10) is a line feed. If you want to see the line feed, tick Wrap Text in the Alignment tab of the Format Cell dialog, but it is not necessary in this sheet. You can even hide the calculated column. I have included the calculated column in the source range of the pivot table, and replaced Date with Date2 as column field, then turned on Wrap Text for the dates.

    If you leave AutoFormat turned on for the pivot table, the column widths and row heights for the dates will be messed up. If you turn off AutoFormat, they won't be updated for the rest of the table. Pick your evil...

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting dates in pivot table (2003)

    Thanks, but this doesn't seem to be sorting properly. If use your suggested formula (I've switched "m" and "d" so I get "3/1" for March 1, rather than 1/3), I DO get the line breaks I need, which is great. But as you can see from the new attached sample, where I've added more dates to the main table, and a new pivot table as Sheet 1, it's not sorting by date properly. Both the Date and Date 2 fields are sorted properly in the source table, but they don't end up that way in the pivot table. It seems to be sorting somehow by the name of the day or something. Did I mess up something, or am I right that there's a problem?

    Also, what I'm seeing in the pivot table in your example are abbreviations for days in the pivot table that don't appear to be English ("di" and "wo"). I'm guessing that we have versions of Excel made for different countries?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting dates in pivot table (2003)

    I am using a Dutch language version of Excel. Since pivot tables do not update automatically, the Dutch day names will remain until you refresh the pivot table.

    Date2 is a text field, so it is formatted alphabetically.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting dates in pivot table (2003)

    Using the char(10) in a custom format always gives the problem with the "###" with numbers/dates etc. The routine isn't "intelligent" enough to realize that it is a word wrap when it calculates the required width. The column must be wide enought to display the entire formatted text as if it were "Unwrapped" (which "defeats" part of the reason for doing it in the first place)

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting dates in pivot table (2003)

    Here is somewhat of a workaround:
    Goto Sheet1
    RIght-click the pivot table and press Wizard
    In "step 3 of 4" drag the "Date" field to the immediate left of the "Date2" field in the "Column Section"
    In the pivot table created,
    right - click on the "Tues 3/1 Total" column
    and press delete
    Right-click on Row4 and select "Hide"

    You can reduce the column widths as desired, but to keep them after a refresh, you must:
    right-click the pivot table
    Choose Options. Uncheck "Autoformat table"
    If the table is autoformatted the widths will be adjusted to the width of the "unwrapped text"

    You can "hide" the Date2 label by dbl-clicking it, and in the "Pivot Table Field Dialog" change the name to a single space (you can not have it blank).
    This will not hide it completely, the "grey box" will still be there, but the name will not.

    Other than the "extra grey box", the only disadvantage I see is the Grand Total label for the column is hidden since it is in row 4 and that row is hidden.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting dates in pivot table (2003)

    Pretty good! Actually, with your method, you can revise the formula for the Date2 cells to just =A2, and then reformat all the cells as just "ddd". That way, you get what I want, but don't have to delete row four. I still, however, have to hide all the "total" columns, which brings me to two questions:

    1. What *are* those columns and how did they get there in the first place?

    2. Following your directions for hiding them doesn't work. Specifically, right clicking on the "Tues 3/1 Total" column does not give me a "delete" option, which is your next step. I've somehow managed to get Excel to select just the "total" columns and then hide them all, but I still can't figure out what I did to make that happen, or to reproduce it reliably. Can you walk me through that part again, and maybe explain why these columns all get selected at once somehow?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting dates in pivot table (2003)

    Right -click on the column name - delete will remove them and you shouldn't have to hide the columns

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting dates in pivot table (2003)

    I guess I still don't understand. If, by column name, you mean the column label (A, B, C, whatever), right-clicking does give me a "delete" option, but when I try that, I get an error message that says "You cannnot change ... cells in a pivot table report." If, by column name, you mean the headings for the new columns (e.g., "Wednesday total"), right clicking on them brings up a lot of options, but "delete" isn't one of them. The only way I've been able to get rid of them is to right click on the column heading ("Wed. total"), go to SELECT - ENABLE SELECTION, and then repeat that. When I repeat it, all the "total" columns get selected at once, and then I can right click on any one of them, clich "HIDE", and they all disappear.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting dates in pivot table (2003)

    Do they stay hidden on refresh?

    Perhaps this is just different functionality in the Pivot tables from XL97 (which I am using) and XL2003. In XL97 I can right click on any of the "total labels", and choose delete.

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting dates in pivot table (2003)

    Excel 2002 doesn't let you delete subtotals in a pivot table either, but right-clicking on any subtotal label and selecting Hide will hide all corresponding subtotal columns. They stay hidden when you refresh the pivot table.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting dates in pivot table (2003)

    Does it essentially do the same thing and it is just a "nomenclature change" or is the pivot routine somehow different (or is this something only the minds of MS programmers can completely understand)?

    Hide to me implies that it is still there, just not displayed (implies "overhead"). In a sheet (for example) if I hide column C, you can tell it is still there, since the col labels are A, B, D, etc. If I delete Col C it is gone...

    Steve

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting dates in pivot table (2003)

    It doesn't hide entire columns, only the part within the pivot table. It is equivalent to turning off Subtotals for the Date field (double click the Date button and set Subtotals to None).

  14. #14
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    San Francisco, California, USA
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting dates in pivot table (2003)

    They stay hiddne, so I guess it amounts to the same thing. Thanks again for your help.

Posting Permissions

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