Solved

Excel graphing time ranges

Posted on 2011-09-08
4
151 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
4 Comments
 
LVL 50

Accepted Solution

by:
teylyn 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now