Custom Number Format for DataLabels/Y-axis in Pivot Chart.

[Ehtisham Ali]

Hi All,

I have a data on which I have plotted a Pivot chart and stuck with a custom number format.

I have an Ageing field formatted in (dd:hh:mm) which is not properly displayed on Pivot chart. I have tried to format it using "Format Datalabels" but had no luck.

Please help me out on this. Refer to the attached file for all I have done.

Please do let me know for any clarification on my query.

 

[Chihiro]

I'd suggest going with [h]:mm instead. See attached.

Since dd signifies dates, it can't interpret 1900 1/00 correctly, since dates can't go lower than 1 or higher than 31.

Alternately, you can express it as "0.00" INT being # of dates and MOD being hours and minutes.
For C2:
dd:hh:mm = 01:22:49
[h]:mm = 46:49
0.00 = 1.95

 

[Ehtisham Ali]

I'd suggest going with [h]:mm instead. See attached.

Since dd signifies dates, it can't interpret 1900 1/00 correctly, since dates can't go lower than 1 or higher than 31.

Alternately, you can express it as "0.00" INT being # of dates and MOD being hours and minutes.
For C2:
dd:hh:mm = 01:22:49
[h]:mm = 46:49
0.00 = 1.95

Hi Chihiro,

Thanks for your quick reply. I my case it is a customer requirement to display ageing in that format (dd:hh:mm) because it makes more sence to them.

Later basis on which I have done some buckting like (0-1 Days,1-2 Days etc).

So give it a try if we can come up with a solution without modifying the query.

 

[Chihiro]

I'd not recommend it as it can cause other issues...
But you need a helper column for label.
Do something like =INT(C2)&":"&TEXT(MOD(C2,1),"hh:mm")

However, this won't solve axis label, for that you'd still need either in [h]:mm or 0.00 format as above won't be recognized as number series.