Solved

Excel Hourly Data

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

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 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

9 Experts available now in Live!

Get 1:1 Help Now