• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Excel graphing time ranges

I'm using excel 2007

I have a range of data thus:
vet_date	vet_time	call_number
06-Sep-11	07:51:00	95000
06-Sep-11	08:15:00	94760
06-Sep-11	08:18:00	94949
06-Sep-11	08:29:00	94979
06-Sep-11	08:34:00	94980
06-Sep-11	08:38:00	92058
06-Sep-11	08:42:00	95118
06-Sep-11	08:43:00	95113
06-Sep-11	09:17:00	94820
06-Sep-11	09:21:00	93087
06-Sep-11	09:27:00	92563
06-Sep-11	09:28:00	93466
06-Sep-11	09:29:00	94847
06-Sep-11	09:30:00	95137
06-Sep-11	09:32:00	93998
06-Sep-11	09:34:00	94272
06-Sep-11	09:36:00	94978
06-Sep-11	09:37:00	93170
06-Sep-11	09:38:00	94524
06-Sep-11	09:46:00	94532
06-Sep-11	09:47:00	94880
06-Sep-11	09:53:00	94186
06-Sep-11	09:55:00	93258
06-Sep-11	09:57:00	91968
06-Sep-11	10:02:00	88577
06-Sep-11	10:02:00	95174
06-Sep-11	10:07:00	94971
06-Sep-11	10:08:00	94302
06-Sep-11	10:09:00	94478
06-Sep-11	10:14:00	95321
06-Sep-11	10:18:00	95233
06-Sep-11	10:24:00	95202
06-Sep-11	10:24:00	95322
06-Sep-11	10:25:00	95314
06-Sep-11	10:27:00	95235

Open in new window


I want a graph that shows volume of calls for a period (IE, 09:00-10:00, 10:00-11:00 etc) for the day.

I cannot for the life of me figure out how to do it.

Any help appreciated.
0
Ross
Asked:
Ross
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

click  a cell in the data table, then

Insert > Pivot Table drop down > Pivot Chart
Select a location for the Pivot chart
Drag Vet_date and vet_time into the Axis Fields area
Drag Call_number into the Values area, click the dropdown and set the Value Field Settings to "Count"
You will now see a Chart and a Table. In the table, right-click any time value and select "Group" from the context menu.

Click Hours and unselect any other highlights in the list.

Hit OK.

The result is attached.

cheers, teylyn
Book2.xlsx
0
 
RossAuthor Commented:
Actually I figured it out a slightly different way. I normalised the times with:

=TIME(HOUR(B2),IF(AND(MINUTE(B2)>=0,MINUTE(B2)<30),0,30),0)

Then based a pivot table and chart of that

I guess either way works.

Thanks for your alternative view :)
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now