Solved

Excel Hourly Data

Posted on 2014-01-07
4
266 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
[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
  • 2
4 Comments
 
LVL 33

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 250 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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 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

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 45
Vlookup - 2 criteria 6 44
I NEED KEEP THE CONDITIONAL  FORMATTING 24 26
Set a Range to a Cell in Excel VBA 2 13
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

749 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