Solved

Excel Hourly Data

Posted on 2014-01-07
4
270 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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