?
Solved

Excel graphing time ranges

Posted on 2011-09-08
4
Medium Priority
?
157 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:RossDagley1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 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:RossDagley1
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:broomee9
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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