+ Reply to Thread
Results 1 to 2 of 2

Charting - Hours of Attendance in a Service

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Charting - Hours of Attendance in a Service

    Hi everyone

    I have times booked to attend a service, for example:

    Booked1.jpg

    I want to create a graph (preferably a pie graph) that shows me the times booked, but I’ll have hundreds of lines.

    Therefore, I want to be able to group the same in/out times together.

    For example:

    Booked2.jpg

    So what I want to see in the graph (using the example), would be a pie graph with only 6 colours. I want to see that 9:00am-2:30pm has the largest wedge of the pie, then 8:00-2:00pm, etc.

    The information that I am trying to get from the graph, is which times are the most popular, next popular etc to least popular.

    Any ideas and assistance would be very helpful.

    Thank you

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Charting - Hours of Attendance in a Service

    Without a sample workbook (translation: I'm too lazy to type in all the data above), I can only offer the concept.

    The way I would do this is:
    - Convert the data into an Excel Table since tables know how big they are so as you add or delete data formulas, pivot tables and charts based on the data keep up.
    - Tables also copy down formulas automatically which will come in handy if you need helper columns.

    I notice that you have a number of "overlaps" in the time. For example 9 Am to 1 PM, 9 AM to 2:30 PM, 9 AM to 5 PM. etc. Do you want these to be treated separately, or do you want to partition the times into "buckets" such as 9 Am to 10 AM? In the case of buckets, all three of these scenarios would fall into the 9 AM to 10 AM bucket.

    If you want separate times then you will need a helper column with a formula like: =Text(A2,"hh:mm a/p") & " to " & Text(A2,"hh:mm a/p") - then put this in a pivot table and chart on the helper column.

    If you want buckets then you'll need a matrix with start of the hour and end of the hour from 0:00 to 23:00 - fill in the helper column with a COUNTIFS for between the two times. Chart off the helper column.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. DOT Hours of Service tracking spreadsheet
    By lakraehe in forum Excel General
    Replies: 0
    Last Post: 10-14-2014, 02:58 PM
  2. [SOLVED] Formulas for Federal Hours of Service From
    By n2music in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-20-2014, 02:32 AM
  3. Replies: 153
    Last Post: 04-30-2013, 04:12 PM
  4. Replies: 6
    Last Post: 11-28-2011, 04:08 AM
  5. Attendance and Hours
    By sexybeast in forum Excel General
    Replies: 5
    Last Post: 06-08-2011, 08:10 AM
  6. Service Hours
    By Psycho_uk in forum Excel General
    Replies: 0
    Last Post: 09-29-2010, 07:45 AM
  7. Calculate 'service hours' between 2 dates/times
    By Sugar Ape in forum Excel General
    Replies: 3
    Last Post: 12-23-2009, 10:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1