Graphing Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a datasheet with time values that are in minutes:seconds format and I need to graph them in some way. However, the only way i am finding to graph them is by manually rounding the number to just minutes. Manually rounding the numbers will not work in the long run because I need to have an automated process and no formula will work with my numbers.

here are some of my numbers in (minutes:seconds)
00001:40
00006:40
00013:20
00020:00
00005:00
00023:20
00008:20

That is the way they are given to me and I would like to use them in that format however I can't figured out how to have those numbers show up on the graph.
 
Hi Lisa -

The problem is that Excel doesn't recognize your values as times. If you
had values like 01:40, 06:40, etc. (without all the extra leading
zeros), Excel would recognize them as hour:minute times, which also is
not what you want.

Here's how to convert them. Assuming the values are in column A,
starting in A1, put this formula in cell B1:

=TIME(0,LEFT(A2,5),RIGHT(A2,2))

The cells will say something like 12:01 AM. Format the cells with a
custom number format. Select the cells, choose Cells from the Format
menu, select the Number tab. Select Custom from the list on the left,
and enter mm:ss in the box on the right. Now your cells look like this:

01:40
06:40
13:20
20:00
05:00
23:20
08:20

and Excel knows they are minutes and seconds. If you need all the extra
zeros, use a custom number format like "000"mm:ss instead (with quotes
around the zeros as shown).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top