CODEWITHSUNDEEP
vbaexcelexcel-charts
Jenny
Jenny

Reputation: 451

having x axis as month in chart

I have an sheet "Result" with an table that displays certain data.

In the table I have column A as week number from 1 till 53.

Not all the weeks have the result filled. there are few weeks with no result.

I am trying to generate an Column Stacked chart out of this Table.

While I am extracting, I could see the details are getting overlapped in the chart and there is a lack of visulaization.

I would like to ask, if there is an way I could convert the weeknumber of the X axis of the chart to Month.

Could anyone tell me how I could do it ?

here is the code I am using for my chart

Sub chartstatus()
Dim rng, rng1, rng2 As Range
Dim cht As Object
Set rng1 = ActiveSheet.Range("A2:A53")
Set rng2 = ActiveSheet.Range("G2:J53")
Set rng = Union(rng1, rng2)
ThisWorkbook.Sheets("Result").ChartObjects.delete
Set sh = ActiveSheet.ChartObjects.Add(Left:=750, _
    Width:=1100, _
    Top:=80, _
    Height:=350)

sh.Select

Set cht = ActiveChart
With cht
.SetSourceData Source:=rng
.ChartType = xlColumnStacked
cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"
cht.SeriesCollection(1).delete
End With
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
cht.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
cht.HasTitle = True
cht.ChartTitle.Text = "Result 2017(Percentage)"
End Sub

This is how my table looks like with the data, and with column A having the Weeknumber with the above code, I am generating an Graph like below.

Instead of the numbers displayed below, I would like to have the corresponding month name. Can anyone help me to achieve this ?

Upvotes: 0

Views: 582

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

If your week numbers are in Column A, use Column B as a helper column and enter the below formula,

=TEXT(42729+(A1*7),"mmm-yy")

enter image description here

You could use this range for the chart axis. Hope this helps.

Upvotes: 1

Tom
Tom

Reputation: 9898

Use a helper column.

In my example I have the week number in Column A and Year in Column B. In Column C I've then used the following formula:

=DATE(B1,1,-2)-WEEKDAY(DATE(B1,1,3))+A1*7

Then instead of using the week number column for you chart use your calculated column. Then format the chart axis with a custom format of "mmmm" This will give you what you're looking for

Upvotes: 1

Related Questions