Re-formatting x-axis months to generic "month 1, month 2, month 3" etc.

heretol3arn · Aug 23, 2021

Hi All -

I've got an issue that I'm trying to resolve, but have been unable to so-far. I am trying to reformat the x-axis values (which are currently in months) to a more generic label. I would like Oct-2021 to be "Month 1" and so on and so forth, so until "Month 33" for Jun-2024.

I've created a stacked horizontal bar below and unfilled the values prior to the start date to build out a Gantt chart. Also, I've tried formatting through custom formats, but I haven't had any luck so far.

Does anyone have any ideas on how to get to the desired result?

bobsan42 · Aug 23, 2021

Create a helper column, fill it with month 1, 2, 3 etc. and use it as x axis if you don't care much about precise date positioning.
If not - fill it with decimal numbers to account for the exct date and use custom number format on x axis afterwards.

JEC · Aug 23, 2021

With a dynamic formula, or just put 1,2,3...and fill down

asdfadf.xlsm

A

B

1

1-okt

Month 1

2

1-nov

Month 2

3

1-dec

Month 3

4

1-jan

Month 4

5

1-feb

Month 5

6

1-mrt

Month 6

7

1-apr

Month 7

8

1-mei

Month 8

9

1-jun

Month 9

10

1-jul

Month 10

11

1-aug

Month 11

12

1-sep

Month 12

13

1-okt

Month 13

14

1-nov

Month 14

15

1-dec

Month 15

16

1-jan

Month 16

17

1-feb

Month 17

18

1-mrt

Month 18

19

1-apr

Month 19

20

1-mei

Month 20

21

1-jun

Month 21

22

1-jul

Month 22

23

1-aug

Month 23

24

1-sep

Month 24

25

1-okt

Month 25

26

1-nov

Month 26

27

1-dec

Month 27

28

1-jan

Month 28

29

1-feb

Month 29

30

1-mrt

Month 30

31

1-apr

Month 31

32

1-mei

Month 32

33

1-jun

Month 33

Blad1

Cell Formulas
Range	Formula
B1:B33	B1	="Month "&COUNTA($A$1:$A$33)-(DATEDIF(A1,$A$33,"m"))

heretol3arn · Aug 23, 2021

bobsan42 said:
Create a helper column, fill it with month 1, 2, 3 etc. and use it as x axis if you don't care much about precise date positioning.
If not - fill it with decimal numbers to account for the exct date and use custom number format on x axis afterwards.

So I'm not entirely sure what you mean by this. I've added a helper column and set up the series, however it doesn't recognize it as a point on the x-axis but rather just as "Jan-00"

heretol3arn · Aug 23, 2021

JEC said:
With a dynamic formula, or just put 1,2,3...and fill down

asdfadf.xlsm
A B
1 1-okt Month 1
2 1-nov Month 2
3 1-dec Month 3
4 1-jan Month 4
5 1-feb Month 5
6 1-mrt Month 6
7 1-apr Month 7
8 1-mei Month 8
9 1-jun Month 9
10 1-jul Month 10
11 1-aug Month 11
12 1-sep Month 12
13 1-okt Month 13
14 1-nov Month 14
15 1-dec Month 15
16 1-jan Month 16
17 1-feb Month 17
18 1-mrt Month 18
19 1-apr Month 19
20 1-mei Month 20
21 1-jun Month 21
22 1-jul Month 22
23 1-aug Month 23
24 1-sep Month 24
25 1-okt Month 25
26 1-nov Month 26
27 1-dec Month 27
28 1-jan Month 28
29 1-feb Month 29
30 1-mrt Month 30
31 1-apr Month 31
32 1-mei Month 32
33 1-jun Month 33
Blad1
Cell Formulas
Range Formula
B1:B33 B1 ="Month "&COUNTA($A$1:$A$33)-(DATEDIF(A1,$A$33,"m"))

Hi JEC - Thank you for your response, in terms of converting the month into "Month 1" that is not the issue. I am trying to get the x-axis on the graph to show Month 1, Month 2, Month 3 instead of "Oct-21" etc.