?
Solved

Excel graphing time ranges

Posted on 2011-09-08
4
Medium Priority
?
163 Views
Last Modified: 2012-05-12
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
Comment
Question by:Ross
3 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36501397
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
 
LVL 2

Author Comment

by:Ross
ID: 36501425
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
 
LVL 24

Expert Comment

by:Tracy
ID: 37169472
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question