help!save my job...charting time is causing me headache!

[kerry78]

Hi there! I have a problem charting some data....
Column A: I have values of 1 through 23 (these are cycles in m
business)
Column B: I have a different time value associated with coresspondin
cycle in format H:mm

Example:
cycle 1 took 29:55 (29hours and 55minutes)

I need to display as a graph/chart for tomorrow am!


Y axis are my cycles
X axis should be intervals of time in increments of 5 hours

Is it not charting properly because time is displayed H:mm- does i
need to be decimal format? Also, the data i am grabbing is from cell
that have formulas attached, so does that cause a problem....?
Thanks everybody!
PS: I am not at work now-so cant attach file for review, but will sen
to anyone willing to help in the a

 

[Stephanie Krieger]

Hi, Kerry,

The H:mm format doesn't mean number of hours and minutes,
it's hour and minute of the day ... so it doesn't really
match what you're looking for (that format can't exceed
24:00)

Using decimals would be more accurate for the numbers you
need if you can display them in that way.

Let me know if you need a work around to display the
numbers in that format.

Stephanie Krieger
author of Microsoft Office Document Designer
(from Microsoft Press)
e-mail: (e-mail address removed)
blog: arouet.net

 

[Tushar Mehta]

Is the time really a time as understood by XL? If so, the format
(Format | Cells... | Number tab) should be [h]:mm and *not* H:mm

Once that is resolved, create a bar chart. Select the column A:B data,
click the chart wizard and select the Bar chart type. This consists of
horizontal bars and will give you what you want.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

You should also check the responses to all the other discussions in the
same newsgroup that you've started on the same subject.
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

The number formats specify how Excel displays a number, which may be
stored internally in a very different way.

Using a number format of [h]:mm allows the display to exceed 24:00. It's
important to know that Excel stores times as a fraction of a 24-hour
day. 0:00 is zero, 6:00 is 0.25, 12:00 is 0.5, 24:00 is 1.0, and 30:00
would be 1.25.

If you used a number format of h:mm, without the brackets, 24:00 would
appear as 0:00 and 30:00 as 6:00, because it would not show beyond 24:00
(or 1.0). Without specifying a date portion of the number format,
there's no way to show the 24-hour units.

Dates are stored as the whole numbers, indicating days since a reference
point of 1 January, 1900, including 29 February, 1900, because everyone
but the Lotus programmers know that 1900 isn't a leap year (and either
the MS programmers kept the error for compatibility, or they didn't know
about non leap years either). Today is 38226. Today at 6 am was
38226.25, and tomorrow at 6 am is 38227.25. You would use a number
format like d mmm yyyy h:mm to display them as 27 Aug 2004 6:00 and 28
Aug 2004 6:00.

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