Solved

Excel graphing time ranges

Posted on 2011-09-08
4
156 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 500 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

Industry Leaders: 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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

717 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