Group data by week but display date format axis

1
votes

I have data which I want to display on an Excel column chart. It represents the number of sales per week, where the date is the first day of the week:

enter image description here

If I leave the dates as dates then Excel interprets this as data for one day out of seven, so I get thin columns with large gaps:

enter image description here

I can resolve this by formatting the dates as text, which gives me the style I want:

enter image description here

However, I want a date scale where only the first of each month is labelled, which I think requires a date formatted axis.

Basically, I want to achieve this in Excel instead of paint:

enter image description here

Any ideas on how (if) this can be done?

2
exceldatecharts
It's not a specfic programming question, but I'll accept a VBA solution if there is one. - ajor
I could also use the answer. If you receive it here or on Superuser, please, let me know. - ZygD
See answer below.... - ajor

2 Answers

2
votes

What you could do is make a line chart on the date axis, add and format error bars, then hide the lines and markers. Use the Minus option for the error bars, with no End Caps, and a Percentage Value of 100%, so the error bar reaches the horizontal axis. Format the error bar lines to simulate a thicker bar.

Error Bars as Column Chart

0
votes

I had to do this recently and the way I got around it was to create a second set of values using the =Text(A1,"MMM YY") formula.

Instructions on your actual sample data:

Insert a column between Week beginning and Sales (For the purpose of this we will assume Week beginning is in column A)

In the new column B row 2 enter this formula:

=IF(TEXT(A2,"MMM YY")<>TEXT(A1,"MMM YY"),TEXT(A2,"MMM YY"),"")

Here we are telling it only to list if it is the first instance of the new month

Copy it down as far as column A has data

Highlight the data in column B and C

Create a column chart

Your chart will now look how you wanted it

Edit: I just noticed you have "Week beginning" but I assume you want your chart to be week ending for the titles?

If so then make the B2 formula

=TEXT(A2+5,"MMM YY")

and then make the B3 one

=IF(TEXT(A3+4,"MMM YY")<>TEXT(A2+4,"MMM YY"),TEXT(A3+4,"MMM YY"),"")

And drag down, I have added 4 on the assumption your week closes on Friday (Monday + 4 Days) but you can adjust that accordingly