I have created a mock up below of what I am trying to create in Excel. I had created the perfect mockup in excel but since we can’t do attachments or screenshots without suing one of this listed addins I’m going to do my best to describe what I was not able to create in the mockup in this post.
It might help to first explain what I am trying do; why I need excel to chart this information. We have a report queue with thousands of reports which in Excel would be listed as rows. Each report item in this queue has multiple pieces of information and those would be columns in Excel. These pieces of data include (but are not limited to): Unique ID, Name/Description, Start Date/Time, End Date/Time & Duration . I can easily export or past e this info into Excel. What I need to figure out now is how to get excel to chart these report queue items similar to how a timeline chart shows multiple events start and end dates along with their duration and with each event stacked above or below so as to see how it compares to other events,
Per the recommendation of a co-worker I looked into a Gannt chart but I’m not so sure it would work. I tried to create one following a tutorial but it got over complicated and failed because the example was using dates and I need to look at time of day as the points of measure.
Using the example I have mocked up below I have the first row setup as column headers with 4 sample rows of data. Each row has a unique ID, a start and end time as well as the duration (End time – start time) measured in seconds.
In my example I have 4 additional columns where each represents an hour in the day starting with 8AM and ending at 11AM. I need for my solution to list all 24 hours . I listed just a few hours here and not all 24 for brevity’s sake. I don’t have to list each hour in oits own column if there is a way to do what I need using 1 column. I just need for it to be wide enough so that I can see the difference between when a report started at 8AM and one that started at 8:01 AM.
Example row #1 (Where unique ID = 1) : This row has a report that started at 8:00AM and completed at 8:15AM. What I need is for excel to chart this so that a line or box/square fills up this column starting at the far left border and ending ¼ of the width of the column. Basically it would fill in the first ¼ of the column thus showing that this item has taken ¼ of the hour beginning at the start of the hour and ending 1/4th of the way into that hour.
Example Row#2 (where Unique ID =2): This report started at 8:15 and ended at 8:30. For this row I want excel to fill the 2/4th section of the cell to show that this report started at 1/4th the way into the hour and completed at the hours mid-point.
In the third row the report started at 8:30 and completed at 9. For this one I need excel to fill the second half of the cell to indicate that the report started at the half way point in the 8AM hour and was done by the very end of that hour.
The 4th and last row has a report that runs across multiple hours starting at 8:30 and ending at 9:30. For this row I want excel to fill the second half of the 8AM cell and the first half of the 9AM cell.
Hopefully by this point I’ve been able to articulate (well enough) what I’m trying to do that someone can make a recommendation on how to proceed. I’m completely open to using anything from charts to spark lines or anything else in Excels tool box. I just need a way top get a visual charting of the days report queue such that I can easily and quickly see when any report started, ended, how long it took and pick out points during the day when the queue was busiest and when it was slowest.
<tbody>
</tbody>
It might help to first explain what I am trying do; why I need excel to chart this information. We have a report queue with thousands of reports which in Excel would be listed as rows. Each report item in this queue has multiple pieces of information and those would be columns in Excel. These pieces of data include (but are not limited to): Unique ID, Name/Description, Start Date/Time, End Date/Time & Duration . I can easily export or past e this info into Excel. What I need to figure out now is how to get excel to chart these report queue items similar to how a timeline chart shows multiple events start and end dates along with their duration and with each event stacked above or below so as to see how it compares to other events,
Per the recommendation of a co-worker I looked into a Gannt chart but I’m not so sure it would work. I tried to create one following a tutorial but it got over complicated and failed because the example was using dates and I need to look at time of day as the points of measure.
Using the example I have mocked up below I have the first row setup as column headers with 4 sample rows of data. Each row has a unique ID, a start and end time as well as the duration (End time – start time) measured in seconds.
In my example I have 4 additional columns where each represents an hour in the day starting with 8AM and ending at 11AM. I need for my solution to list all 24 hours . I listed just a few hours here and not all 24 for brevity’s sake. I don’t have to list each hour in oits own column if there is a way to do what I need using 1 column. I just need for it to be wide enough so that I can see the difference between when a report started at 8AM and one that started at 8:01 AM.
Example row #1 (Where unique ID = 1) : This row has a report that started at 8:00AM and completed at 8:15AM. What I need is for excel to chart this so that a line or box/square fills up this column starting at the far left border and ending ¼ of the width of the column. Basically it would fill in the first ¼ of the column thus showing that this item has taken ¼ of the hour beginning at the start of the hour and ending 1/4th of the way into that hour.
Example Row#2 (where Unique ID =2): This report started at 8:15 and ended at 8:30. For this row I want excel to fill the 2/4th section of the cell to show that this report started at 1/4th the way into the hour and completed at the hours mid-point.
In the third row the report started at 8:30 and completed at 9. For this one I need excel to fill the second half of the cell to indicate that the report started at the half way point in the 8AM hour and was done by the very end of that hour.
The 4th and last row has a report that runs across multiple hours starting at 8:30 and ending at 9:30. For this row I want excel to fill the second half of the 8AM cell and the first half of the 9AM cell.
Hopefully by this point I’ve been able to articulate (well enough) what I’m trying to do that someone can make a recommendation on how to proceed. I’m completely open to using anything from charts to spark lines or anything else in Excels tool box. I just need a way top get a visual charting of the days report queue such that I can easily and quickly see when any report started, ended, how long it took and pick out points during the day when the queue was busiest and when it was slowest.
| Unique ID | Start | End | Seconds | 8AM | 9AM | 10AM | 11AM |
| 1 | 2016/10/30 - 08:00 | 2016/10/30 - 08:15 | 900 | ||||
| 2 | 2016/10/30 - 08:15 | 2016/10/30 - 08:30 | 900 | ||||
| 3 | 2016/10/30 - 08:30 | 2016/10/30 - 09:00 | 1800 | ||||
| 4 | 2016/10/30 - 08:30 | 2016/10/30 - 09:30 | 3600 |
<tbody>
</tbody>
