Help creating a time occupancy graph - Excel 2003

M

mamepes

New Member
Joined
Dec 7, 2012
Messages
1
Hi guys,

I'm trying to create a task occupancy graph for some of the work I'm doing at the moment. The data we are collecting is arranged as demonstrated below.
So, for example, we record that a communication a started at 21:53:58 and finished at 21:54:09, then the next one started at 21:54:51 and finished at 21:55:13, and so on.

[TABLE="width: 343"]
<TBODY>[TR]
[TD]Start Time</SPAN>
[/TD]
[TD]End Time</SPAN>
[/TD]
[TD]Finished?</SPAN>
[/TD]
[TD]Activity</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:53:58</SPAN>
[/TD]
[TD="align: right"]21:54:09</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Comms</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:54:18</SPAN>
[/TD]
[TD="align: right"]21:54:23</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Paperwork</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:54:27</SPAN>
[/TD]
[TD="align: right"]21:54:35</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:54:51</SPAN>
[/TD]
[TD="align: right"]21:55:13</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Comms</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:55:17</SPAN>
[/TD]
[TD="align: right"]21:55:32</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:55:39</SPAN>
[/TD]
[TD="align: right"]21:55:56</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:55:59</SPAN>
[/TD]
[TD="align: right"]21:56:08</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:56:16</SPAN>
[/TD]
[TD="align: right"]21:56:27</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:57:32</SPAN>
[/TD]
[TD="align: right"]21:57:50</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TRUST </SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:57:58</SPAN>
[/TD]
[TD="align: right"]21:58:07</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:58:10</SPAN>
[/TD]
[TD="align: right"]21:58:32</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Active monitoring</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:58:38</SPAN>
[/TD]
[TD="align: right"]21:58:50</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:58:53</SPAN>
[/TD]
[TD="align: right"]21:58:59</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Comms</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:59:19</SPAN>
[/TD]
[TD="align: right"]21:59:32</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]21:59:40</SPAN>
[/TD]
[TD="align: right"]21:59:48</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Comms</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]22:00:04</SPAN>
[/TD]
[TD="align: right"]22:00:09</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]Route setting</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]22:00:25</SPAN>
[/TD]
[TD="align: right"]22:00:39</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]22:00:45</SPAN>
[/TD]
[TD="align: right"]22:00:49</SPAN>
[/TD]
[TD]ok</SPAN>
[/TD]
[TD]TRUST </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


What we need to do is to create a time occupancy graph for each type of activity. We have nine activities: Comms, TD, Route setting, etc and the graph needs to show how much time was recorded for each activity and when. The way we found to be able to put the graph together was to sort out the data into the categories and then calculate the duration of each event and the gap of time between the events of each category.

[TABLE="width: 640"]
<TBODY>[TR]
[TD][/TD]
[TD]start time</SPAN>[/TD]
[TD]duration</SPAN>[/TD]
[TD]gap</SPAN>[/TD]
[TD]duration</SPAN>[/TD]
[TD]gap</SPAN>[/TD]
[TD]duration</SPAN>[/TD]
[TD]gap</SPAN>[/TD]
[TD]duration</SPAN>[/TD]
[TD]gap</SPAN>[/TD]
[/TR]
[TR]
[TD]Reminders</SPAN>[/TD]
[TD="align: right"]23:22:31</SPAN>[/TD]
[TD="align: right"]00:01:24</SPAN>[/TD]
[TD="align: right"]00:20:59</SPAN>[/TD]
[TD="align: right"]00:00:12</SPAN>[/TD]
[TD="align: right"]00:40:10</SPAN>[/TD]
[TD="align: right"]00:00:05</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TD</SPAN>[/TD]
[TD="align: right"]21:55:39</SPAN>[/TD]
[TD="align: right"]00:00:17</SPAN>[/TD]
[TD="align: right"]00:00:20</SPAN>[/TD]
[TD="align: right"]00:00:11</SPAN>[/TD]
[TD="align: right"]00:02:11</SPAN>[/TD]
[TD="align: right"]00:00:12</SPAN>[/TD]
[TD="align: right"]00:01:35</SPAN>[/TD]
[TD="align: right"]00:00:14</SPAN>[/TD]
[TD="align: right"]00:14:12</SPAN>[/TD]
[/TR]
[TR]
[TD]TRUST </SPAN>[/TD]
[TD="align: right"]22:08:28</SPAN>[/TD]
[TD="align: right"]00:00:12</SPAN>[/TD]
[TD="align: right"]00:05:56</SPAN>[/TD]
[TD="align: right"]00:00:12</SPAN>[/TD]
[TD="align: right"]00:06:42</SPAN>[/TD]
[TD="align: right"]00:00:12</SPAN>[/TD]
[TD="align: right"]00:03:50</SPAN>[/TD]
[TD="align: right"]00:00:23</SPAN>[/TD]
[TD="align: right"]01:09:37</SPAN>[/TD]
[/TR]
[TR]
[TD]Active monitoring</SPAN>[/TD]
[TD="align: right"]21:58:10</SPAN>[/TD]
[TD="align: right"]00:00:22</SPAN>[/TD]
[TD="align: right"]00:37:31</SPAN>[/TD]
[TD="align: right"]00:00:58</SPAN>[/TD]
[TD="align: right"]00:38:23</SPAN>[/TD]
[TD="align: right"]00:00:11</SPAN>[/TD]
[TD="align: right"]00:12:40</SPAN>[/TD]
[TD="align: right"]00:00:28</SPAN>[/TD]
[TD="align: right"]00:21:44</SPAN>[/TD]
[/TR]
[TR]
[TD]Comms</SPAN>[/TD]
[TD="align: right"]21:53:58</SPAN>[/TD]
[TD="align: right"]00:00:11</SPAN>[/TD]
[TD="align: right"]00:00:42</SPAN>[/TD]
[TD="align: right"]00:00:22</SPAN>[/TD]
[TD="align: right"]00:03:40</SPAN>[/TD]
[TD="align: right"]00:00:06</SPAN>[/TD]
[TD="align: right"]00:00:41</SPAN>[/TD]
[TD="align: right"]00:00:08</SPAN>[/TD]
[TD="align: right"]00:01:26</SPAN>[/TD]
[/TR]
[TR]
[TD]CCF</SPAN>[/TD]
[TD="align: right"]21:57:32</SPAN>[/TD]
[TD="align: right"]00:00:18</SPAN>[/TD]
[TD="align: right"]00:02:55</SPAN>[/TD]
[TD="align: right"]00:00:04</SPAN>[/TD]
[TD="align: right"]00:00:16</SPAN>[/TD]
[TD="align: right"]00:00:11</SPAN>[/TD]
[TD="align: right"]00:04:44</SPAN>[/TD]
[TD="align: right"]00:00:09</SPAN>[/TD]
[TD="align: right"]00:00:25</SPAN>[/TD]
[/TR]
[TR]
[TD]Route setting</SPAN>[/TD]
[TD="align: right"]21:54:27</SPAN>[/TD]
[TD="align: right"]00:00:08</SPAN>[/TD]
[TD="align: right"]00:00:42</SPAN>[/TD]
[TD="align: right"]00:00:15</SPAN>[/TD]
[TD="align: right"]00:00:27</SPAN>[/TD]
[TD="align: right"]00:00:09</SPAN>[/TD]
[TD="align: right"]00:01:50</SPAN>[/TD]
[TD="align: right"]00:00:09</SPAN>[/TD]
[TD="align: right"]00:01:12</SPAN>[/TD]
[/TR]
[TR]
[TD]Paperwork</SPAN>[/TD]
[TD="align: right"]21:54:18</SPAN>[/TD]
[TD="align: right"]00:00:05</SPAN>[/TD]
[TD="align: right"]00:07:44</SPAN>[/TD]
[TD="align: right"]00:01:15</SPAN>[/TD]
[TD="align: right"]01:00:18</SPAN>[/TD]
[TD="align: right"]00:00:03</SPAN>[/TD]
[TD="align: right"]00:21:03</SPAN>[/TD]
[TD="align: right"]00:00:01</SPAN>[/TD]
[TD="align: right"]00:23:13</SPAN>[/TD]
[/TR]
[TR]
[TD]fax</SPAN>[/TD]
[TD="align: right"]22:06:12</SPAN>[/TD]
[TD="align: right"]00:00:18</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=3><COL span=6></COLGROUP>[/TABLE]


I can send you a copy of the graph we need. One that we did by hand by seperating each task and calculating the values.


Any help will be greatly appreciated!
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
168
admvlad
A
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
269
shift-del
shift-del
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
588
awoohaw
awoohaw

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top