+ Reply to Thread
Results 1 to 2 of 2

Pivot Table, Format Days Grouped into Weeks as "mmm-dd" of the First Day of that Week

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Question Pivot Table, Format Days Grouped into Weeks as "mmm-dd" of the First Day of that Week

    My data has these headers:
    Date, Customer, User, MyNumber

    The MyNumber column shows the number of things (doesn't matter, that's not the real header) that the User has for that specific date. Any quantity of Users for a Customer.

    I have multiple Pivot Tables that show different things:
    1. Sum of MyNumber of all Users from a certain Customer, by Month
    2. Sum of MyNumber of all Users from a certain Customer, by Week
    3. MyNumber of each User from a certain Customer, by Week

    Pivot Tables 2 and 3 share a data cache while 1 is separate (for date grouping purposes).

    I've displayed the data from each table in three different Pivot Charts showing the progression of MyNumber over a period of time. Having grouped the dates in Pivot Table 1 by month, the Chart conveniently shows just the month in the format "mmm" (which is exactly what I want).

    Here's where I've hit a snag...

    When grouping the dates of Pivot Table 2 and 3 (the weekly charts), it becomes formatted like "yyyy-mm-dd - yyyy-mm-dd" where the first date shown is the first day of the week and the second is the last day of the week. This doesn't translate nicely to a Chart as even if selecting only a few months of data, the axis grid labels have to be at an angle and are still quite long. I'd rather format it like "mmm-dd" taking just the first day of the week and ignore the middle and last days... But I don't know how. I have a formula that will format it the way I want:
    =TEXT(DATE(LEFT(C1,4),MID(C1,6,2),MID(C1,9,2)),"mmm-dd")
    But you can't use a formula like this as a Custom Number Format to my knowledge. I'm not sure what else to try.
    Last edited by bpiereder; 10-15-2018 at 09:56 AM.

  2. #2
    Registered User
    Join Date
    01-02-2013
    Location
    Toronto, ON, Canada
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Pivot Table, Format Days Grouped into Weeks as "mmm-dd" of the First Day of that Week

    Further research leads me to believe this is impossible, though I'm no expert. I'd love to be proven wrong.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Averages for "x" days/weeks (user input)
    By mjmartin in forum Excel General
    Replies: 4
    Last Post: 03-06-2017, 04:48 PM
  2. Replies: 6
    Last Post: 02-16-2014, 07:15 PM
  3. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  4. [SOLVED] Conditionally format dates for "This week" AND "This month"?
    By Vermilion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2012, 09:20 AM
  5. Pivot: days to week, filling blank weeks
    By jhaquo in forum Excel General
    Replies: 12
    Last Post: 03-23-2011, 02:50 PM
  6. Convert Pivot Table data to "regular" format
    By bronsonb in forum Excel General
    Replies: 1
    Last Post: 02-24-2011, 12:24 AM
  7. [SOLVED] Keep conditional format when "show pages" from Pivot table
    By Angus in forum Excel General
    Replies: 7
    Last Post: 06-30-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1