Trouble with a chart

[odonovanc]

Having trouble with a chart. The date is in hours format. So it'll say something like 3:02.

However, it is not formatted in excel. If I look at format cells to see how it is formatted it just shows as regular number.

Anyway when I go to make a line chart with the data, it does not even try to give me an axis that uses these numbers. No matter how I try to change the data, I cannot get the chart to pick it up. Any tricks?

 

[Jon Peltier]

3:02 is in Hours:Minutes format, or h:mm. Select the range, press Ctrl+1 to format it, and select one of the time formats or just select custom and enter h:mm.

Note that 3:02 am is really 0.126388888888889 and 3:02 pm is really 0.626388888888889, since Excel stores dates and times as whole numbers for dates (the number of days since 1 Jan 1900, give or take an erroneous leap day) and fractions for times (the fraction of a day since midnight, where noon = 0.5).

Set up the data with the formatted times in one column and the values starting in the next adjacent column to the right. Leave a blank cell above the times, and put a label above the values. Select and plot.

Note also that if you use a line chart, even if Excel recognizes the times as times, everything will plot on the same horizontal position, since line charts only plot whole numbers. So make an XY Scatter chart. You can format it with lines just like a line chart, but the X axis will plot times as numerical values.