I am currently trying to do a graph reflecting the time spent per phone call. I need to be specific in the amount of minutes each call is but can't seem to get the y-axis to reflect the values correctly. I have tried using seconds and breaking it down to minutes:seconds but can't get it right.
DURATION Duration (Minutes) 3222 53 Min 42 Sec 2833 47 Min 13 Sec 2753 45 Min 53 Sec 2220 37 Min 0 Sec 2175 36 Min 15 Sec 2154 35 Min 54 Sec 1953 32 Min 33 Sec 1905 31 Min 45 Sec 1845 30 Min 45 Sec 1803 30 Min 3 Sec 1795 29 Min 55 Sec 1764 29 Min 24 Sec 1635 27 Min 15 Sec 1607 26 Min 47 Sec 1589 26 Min 29 Sec 1582 26 Min 22 Sec 1509 25 Min 9 Sec 1505 25 Min 5 Sec 1504 25 Min 4 Sec 1478 24 Min 38 Sec 2 Answers
In column A, have the duration in seconds.
In column B, use this formula to convert it to fractions of a day: =B2/3600/24
Now format column B using this custom format, so it displays as desired: m" min" s" sec"
Be sure to include the double quotes as shown in my string; they mark the parts of the format string that should be shown literally.
Now you can use the formatted column in your graph.
- Convert your data to an Excel Table (it's a good practice).
- Add a helper column (or replace your Duration(min) column) with the following formula
=TIME(,,[@Duration]) - Format your Time column as Time (cumulative hh:mm:ss, represented in the Type list as 37:30:55)
- Create a bar chart with the Time column as your data series.
- Format your horizontal axis for the time intervals you like. For example, you could set up the following values: Min=0 Max=0.0416666 (one hour) Major=0.0069444 (ten minutes) Minor=0.0006944 (one minute)
- Format everything else to taste.
Since Excel uses serial time/date where 1=1 day=24 hours, divide as appropriate for the increments you'd like.
