Excel chart's data is in Days, can I show the Series Data Lables in Months

Question

My chart's range data ("A1:E6") looks is in the screen-shot below:

And this is my current Projects Timeline chart (Type Stacked-Bar):

As you can see, my Series Data Labels are in the same format as my Range, which is days.

Question: is there a way (without VBA) to format the Data Labels to be in months ?

For instance, for Project 3, Stage 1 (brown bar), instead of 90, it will display 3.

Is there a way to "out-smart" the screen below:

Answer 1

You can use helper columns in your data to calculate the desired value you want to show in the labels, i.e. divide by 30 to arrive at the approximate month value for any given number of days.

Then, depending on your Excel version and/or preference and/or need for backwards compatibility, you can either

• manually edit each data label, hit F2 to edit the data label, type a = sign and then click the cell that has the calculated month value. Repeat for each data label, or
• if that sounds too tedious, download and install the free XY Chart Labeler tool by Rob Bovey, which automates the steps above. You can share the file with people who don't have the tool, since all label references will end up hard-coded. The tool just helps reduce the manual labour to link labels to the cells. Or
• with Excel 2013 or higher, use the formatting option in the Data Labels side panel to use "Values from cells" and select the cells where the converted values are. This feature is not backwards compatible and will show placeholder text instead of the labels if the file is opened in Excel versions earlier than XL 2013.

Edit: By the way, the formatting section that you highlighted in your screenshot is for formatting the numbers that the label shows. A format can only change the display of a value, but not perform a calculation, like convert number of days into number of months. So, the format approach is a dead end from the get-go.