Solved

Excel Hourly Data

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

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 32

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

825 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