How do I create a time / hourly axis chart?

[jonbarrett]

Hi,

I want to create a graph that shows the hours of the day on the x-axis and plot values along it from the y-axis.

The time intervals of the values that will be in a table, and plotted against the y-axis, are irregular i.e. a value at 9.00, 10.15, 11.34, 15.04 etc.

I have looked at using 'time-scale' axis but this only seems to treat all the values as a single date and not in an hourly manner.

I need to have a graph that plots the values in the right position relative to the time they occur on a 24 hour axis.

I hope this is clear enough.

Any ideas would be appreciated.

Thanks,

Jon

 

[Mark W.]

What chart type are you going to use?

 

[jonbarrett]

I'm using a line chart. The version of excel I have is 2000.

 

[Mark W.]

You'll need to use a Category axis type, then summarize, and sort your chart's source data. See the Excel Help topic for "About using dates in charts". Notice the notes in the "Time-scale charts and times" paragraph.

 

[jonbarrett]

Thanks for your response.

I checked out the Excel Help and it says the following:

"Time-scale charts and times You can't create time-scale charts from data that is measured at intervals of hours, minutes, or seconds. Only days, months, and years are considered base units in time-scale charts."

Does this mean that excel cannot cope with producing a graph based on time rather than date data?

Here is an example of what I'm trying to achieve:

The data would look like this:

Time Value
10:30:00 2
11:56:00 3
13:32:00 4
16:11:00 1
19:35:00 2

I want to create a line graph that plots the values against an x-axis that it related to time, i.e. the x-axis would be 24 hours, and the values plotted at the right point in time.

Any further ideas would be appreciated.

Thanks, Jon

 

[tmischler]

Would a scatter graph be of any use?

 

[tusharm]

Use a XY Scatter chart.

I'm using a line chart. The version of excel I have is 2000.

 

[tmischler]

tusharm

I thought the same thing and if he wanted a line graph, he could join the points manually but is there a macro that could do that?

I went down the route of seriescollection.points(x).

but couldn't find a method or property to tell excel where to start / finish the line - do you have any thoughts?

 

[tusharm]

No need to join anything manually. One of the options (sub-charts) for the XY Scatter chart is a chart with lines.

tusharm

I thought the same thing and if he wanted a line graph, he could join the points manually but is there a macro that could do that?

I went down the route of seriescollection.points(x).

but couldn't find a method or property to tell excel where to start / finish the line - do you have any thoughts?

 

[jonbarrett]

Spot on! That works great, and by adding the date to the time I can follow one day's values with another days and it plots it against a minutes/hours x-axis.

Cheers.