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

[bpiereder]

Originally posted on Excel Forum, but I haven't had any responses so posting to OzGrid and MrExcel.

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.

 

[bpiereder]

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