Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Hourly Data

Posted on 2014-01-07
4
Medium Priority
?
298 Views
Last Modified: 2014-02-18
I have an excel file that lists events, one per row, with a Start Date/Time and End/Date Time. (within the same day).

I want to expand this data so I can report on hourly usage.

For example, I have an event that begins 12/1/2012 8:00am and ends 12/1/2012 5:00pm. I want to show that the event took place at 8am, 9am, 10am, 11am, 12pm...etc. and eventually put this into a bar chart. Any suggestions?

Thanks
0
Comment
Question by:bezellvi
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 39762685
Are you hoping that the bar chart will have a column/bar that represents 9 hours? Rather than a column/bar for one occurence at 8am, one at 9am etc.

If the former, you can simply get the duration by using a formula

= FinishTime-StartTime

Finish and Start Time being cell references for those times.

This assumes that the data is in true date & time format.

Thanks
Rob H
0
 

Author Comment

by:bezellvi
ID: 39762697
Thanks. I've got the duration, but I have many events I want to depict. So If I have 5 meetings that are from 8am-5pm and 4 meetings that are from 8am-12pm, I want the 8am,9am,10am,11am and 12pm bars to show 9 meetings and the 1pm-4pm bars to show 5 meetings. Does that make sense?
0
 
LVL 8

Accepted Solution

by:
5teveo earned 1000 total points
ID: 39762715
Solution depends upons your Original Event Excel file and its data content formatting...

So what does text in event line look like. What is pattern?

If you see a pattern then parse Event Start date,  Event Start Time, Event End date, Event End Time into seperate columns then...

you could... Build formula's in columns to the right of event that change based upon hour logic. So you would need 24 (or less) columns with formula's watching for specific hours of day and then trigger a color change if true.

This would give you events down the left side of table w/ dates and hours (kinda graphed) in columns across table.  
 
Just a thought...
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 39764881
How many days worth of data will you be showing on the chart?

Expanding on suggestion by 5teveo:

Columns to right of Event data with one column per hour and formula to check if time of event is included in that column; 1 or 0 result. Should be fairly simple check - Column Time is greater than or equal to Start Time AND is less than or equal to Finish Time.

Sum at bottom of column showing total number of meetings, this total then used as Data series for chart.

Issues anticipated:
Total list has multiple dates, chart may only need one day - filter required.

Thanks
Rob H
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

971 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