Charting time values on X\Y axis not formatting correctly

Question

Hello Everyone,

I am trying to chart time values in Excel 2010, Windows 7 Enterprise 64x 8.00gb RAM, I have tried numerous things to try and get the values to display as hh:mm in the axis but I all get is the integer value displayed.

I have tried formatting as text both in the cells and in the axis formatting properties, this has not yielded any success.  I have also tried various chart types, all to no effect.

Is this a bug, if so when can we expect a fix from Microsoft, if not what do I need to do to get these values to display as desired?

thank you all in advance.

Regards

Jag

Answer 1

OK, I see the file. 

On the tab "Average Days to Resolve", I see a chart with columns and a line. The line series is defined to take its values from a range name "AvgDifference". That range name points to ='Group Report'!$D$2:$D$12  and the values in that range are NOT TIME VALUES. They seem to be the total number of resolved tickets.  These are the Y values plotted on the secondary Y axis: numbers that range from around 5000 to around 13000. The Y axis is set to a major unit of 0.0208333. 

Next to that data called "AvgDifference" is another column with data labeled "Avg Time to resolve", but these are not time values either. 

Can you please explain what you would like the chart to show? What should the red line show? If you think these need to be time values, are we talking about elapsed time (like, it took 48 hrs to resolve the ticket) or are we talking about a time of the day, like something happened at 3:30 PM

Where should these time values come from? You say above that "the column that I am using for the time value is already in the hh:mm format ". What column is that? Your data does not point to it.

By the way, it is not good practice to hide data by formatting it white on white. Looking at data ranges and range names, they appear to be pointing at empty cells and can cause quite some confusion (i.e. wasted time) when trying to troubleshoot something.

Answer 2

The only time base column is E, this has the average time take to resolve a call, the other 2 columns are just to total the calls open or being worked on, the other is for closed\resolved calls only as as such should be general formatting.

I tend to like range names as they are easy to manage in VBA for large ranges of data.

I have checked the ranges used in that chart and the range "AvgDifference" it is pointing to the range it should do, so that piece of information is confusing for me.

So this chart and pother intend doing are to show ope\active calls, Closed Resolved calls and the average duration they were open for in that given month.

As for the white on white, cheats way around a situation, I would normally have the data on a seperate sheet so that it is not visible to the user.

I very much appreciate the time you have spent on this with me, thank you.

Answer 3

Please check again. This is the Series Formula for the red line:

=SERIES('Average Days to Resolve'!$E$1,ITSM_ReportingCopy.xlsm!YearsDays,ITSM_ReportingCopy.xlsm!AvgDifference,3)

The range name AvgDifference points to ='Group Report'!$D$2:$D$12

Do you see something different? Are we looking at the same file?

In any case, if you want to use the values in column E of the sheet, be aware that this will not work,either.

The values are going into thousands of days, formatted to show only time. That does not reflect what the values are about.  How do you arrive at these values? How can a call that was opened this year have over 3000 days elapsed?

If a call took 4.99222222 days to close, then the time showing in the cell should be 119:48, i.e. 119 hours and 48 minutes. The cell currently shows 23:48, because it only takes the decimals into account and omits the whole number. 

Once you have sorted these values, we can look at how to chart them.