Custom Chart Data Lables format from % / # / h:mm:ss

[lolls]

Hi all,

I have a dynamic dashboard mini chart built that populates from a drop down box used to select some data that's reported in months. The data set has different UOM - some are in % some are numbers and others in minutes. Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[/TR]
[TR]
[TD]Eggs[/TD]
[TD]17%[/TD]
[TD]19%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]0:8:1[/TD]
[TD]0:16:01[/TD]
[TD]0:18:00[/TD]
[/TR]
</tbody>[/TABLE]

The chart I have built will populate the data (I can not use a dynamic table as there are formulas built into the data) however I have to manually to change the number format for the chart labels to show the label correctly i.e. going from a number to a %

I tried to get around this by making a Custom Format Code (first ever attempt at this) in the Format Data Labels but I cannot seem to get it right, as there are "0's" in the data and there are minutes.

[<1.25] 0.0%;[>1.2] #,###

I don't mind if I have to use VBA to get this working but would prefer an easier solution.

Hope this is clear enough to understand! thanks!