Excel – Chart labeling: durations in minutes and seconds

axischartsdate timemicrosoft-excel-2010

I am attempting to chart the durations of phone interactions. They are currently entered as time of day values (I did not build the workbook), even though they are actually duration values, and Excel utterly refuses to build a chart with them, giving me a complexity error on an otherwise well-formed chart plan.

I am attempting to get a chart that displays these values as minutes and seconds in the Y-axis with the dates of the interactions in the X-axis. The problem is that in the Y-axis Excel (2010) shows a distinct preference for clear numerical values, and my data is currently entered as hh:mm:ss (directly, not calculated).

I want the chart to display how many minutes and seconds on average were spent on a specific type of call on a specific date. I have been able to convert one set of values on the sheet to a whole number using the SECONDS function and gotten at the very least a usable chart — but apparently that only works with time values of less than one minute (60 seconds). I have another column I wish to chart that displays time in minutes and seconds with a usual minute value of 1. The MINUTE function coverts these figures to straight minutes with no second values — not particularly useful for these values (because the distinctions I am looking to graph are in a range of several seconds).

Do I have a third option to get the values I seek?

Best Answer

It turns out you can do what you want with the built-in formatting options. Just plot the duration times as they are stored (time values). Right-click on the Y axis and select Format Axis | Number. Among the time formats is a standard format mm:ss.0;@. Select that and it will appear in a customization box. Edit it to read: mm"m"ss"s" and click the Add button. The axis labels will be formatted like 05m27s.

Related Solutions

Excel – How to rename the values on Y Axis in Excel

It can be done with a bit of trickery, but if it's a simple chart, it's almost definitely easier to just manually draw some new labels using text boxes with opaque backgrounds over the existing labels.

But...

Hide the existing Y-axis ticks and labels, then plot a new X-Y series of points like this:

By changing the format of the point markers to something suitable, like horizontal lines,you'll get a new set of tickmarks straight up the y-axis.

Unfortunately this is where it gets a little more fiddly. You now need to plot a data label for each point to form your new y-axis tick labels. As far as I know not even the latest version of Excel can do this automatically, but you'll find various macros to do this for you (Google: Excel X-Y scatter point labeller).

You'll then be able to add an extra column:

x y label

0 1 f 

0 2 e

0 3 d

0 4 c

. . .

Run the macro and each pseudo-tickmark will have a label next to it. But you'll need to play with label alignment settings to get them in the right place.

Excel: Charting date, time elapsed, and another value

As far as I know the function box shows the time in the system format. Why is it important how the function box display the time? It only means excel detect it is in the datetime format (usually a good thing). From there you can customize how the cells display that datetime (this is where you choose the correct format

If you want the time displayed as 14:04:05 in your cells (hence, in your graphic) you can use this custom format: "hh:mm:ss" or pick one of the predefined datetime format.

If you want to extract the numbers to do calculation with them you can use the following formulas: hour(yourdatetimecell) minute(yourdatetimecell) second(yourdatetimecell)

EDIT : You can work your way with converting your datetime into a sum of minute or a sum of seconds and graph it just like any other number. + Using the scatter graph format I have no issue creating a graph of duration expressed as hh:mm:ss (even if theres an am/pm or even a date showing up in the function bar)

If I'm to chart the following making sure to use two different Y axis for the value of the second and 3rd columns, scatter plot works fine.

11/12/2012  10  4:55:08
12/12/2012  10  3:55:08
1/12/2013   11  4:55:08
2/12/2013   12  7:55:08
3/12/2013   11  3:55:08
4/12/2013   10  7:55:08
5/12/2013   9   6:55:08
6/12/2013   12  6:55:08
7/12/2013   15  14:06:38
8/12/2013   16  48:05:39

Related Question