Hello,
I have a large range of data, with each row representing an individual task. Much of the range is populated with external data. Typically, each task takes anywhere from several minutes to several hours, and there are a number of tasks performed each day, sometimes simultaneously.
The three columns relevant to my problem are A) Start Time, B) End Time, and C) Minutes. The minutes column represents the total amount of time spent on an individual task. Because the tasks frequently overlap in time, I am having trouble calculating the daily time spent on all tasks. Here is some example data:
<table style="border-collapse: collapse; width: 214pt;" border="0" cellpadding="0" cellspacing="0" width="284"><col style="width: 86pt;" width="114"> <col style="width: 80pt;" width="106"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 86pt;" height="17" width="114">Start Time</td> <td class="xl68" style="width: 80pt;" width="106">End Time</td> <td class="xl68" style="width: 48pt;" width="64">Min</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:01</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 19:03</td> <td style="width: 48pt;" align="right" width="64">116</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 17:44</td> <td style="width: 48pt;" align="right" width="64">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:51</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 18:07</td> <td style="width: 48pt;" align="right" width="64">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 18:36</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 20:21</td> <td style="width: 48pt;" align="right" width="64">102</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 19:13</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 20:10</td> <td style="width: 48pt;" align="right" width="64">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 20:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 21:37</td> <td style="width: 48pt;" align="right" width="64">81</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 18:36</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 20:45</td> <td style="width: 48pt;" align="right" width="64">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 18:46</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 19:08</td> <td style="width: 48pt;" align="right" width="64">22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 19:56</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 20:11</td> <td style="width: 48pt;" align="right" width="64">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 20:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 22:04</td> <td style="width: 48pt;" align="right" width="64">104</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 20:58</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 23:16</td> <td style="width: 48pt;" align="right" width="64">139</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 21:25</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 21:27</td> <td style="width: 48pt;" align="right" width="64">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 21:30</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 21:40</td> <td style="width: 48pt;" align="right" width="64">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 22:25</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 23:19</td> <td style="width: 48pt;" align="right" width="64">54</td> </tr> </tbody></table>
I'm at a loss for what to do to get the correct results here, especially because the number of cells overlapped and degree of overlap between two cells vary widely. Taking the bottom four rows as an example, adding the Minutes column gives 206 minutes, whereas I need to know the total time spent performing tasks - 141 minutes (20:58 through 23:19). Taking the earliest start time and last end time is insufficient because there are often several breaks between tasks.
Any help would be greatly appreciated.
I have a large range of data, with each row representing an individual task. Much of the range is populated with external data. Typically, each task takes anywhere from several minutes to several hours, and there are a number of tasks performed each day, sometimes simultaneously.
The three columns relevant to my problem are A) Start Time, B) End Time, and C) Minutes. The minutes column represents the total amount of time spent on an individual task. Because the tasks frequently overlap in time, I am having trouble calculating the daily time spent on all tasks. Here is some example data:
<table style="border-collapse: collapse; width: 214pt;" border="0" cellpadding="0" cellspacing="0" width="284"><col style="width: 86pt;" width="114"> <col style="width: 80pt;" width="106"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 86pt;" height="17" width="114">Start Time</td> <td class="xl68" style="width: 80pt;" width="106">End Time</td> <td class="xl68" style="width: 48pt;" width="64">Min</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:01</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 19:03</td> <td style="width: 48pt;" align="right" width="64">116</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 17:44</td> <td style="width: 48pt;" align="right" width="64">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 17:51</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 18:07</td> <td style="width: 48pt;" align="right" width="64">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 18:36</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 20:21</td> <td style="width: 48pt;" align="right" width="64">102</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 19:13</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 20:10</td> <td style="width: 48pt;" align="right" width="64">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/11/09 20:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/11/09 21:37</td> <td style="width: 48pt;" align="right" width="64">81</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 18:36</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 20:45</td> <td style="width: 48pt;" align="right" width="64">123</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 18:46</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 19:08</td> <td style="width: 48pt;" align="right" width="64">22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 19:56</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 20:11</td> <td style="width: 48pt;" align="right" width="64">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 20:16</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 22:04</td> <td style="width: 48pt;" align="right" width="64">104</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 20:58</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 23:16</td> <td style="width: 48pt;" align="right" width="64">139</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 21:25</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 21:27</td> <td style="width: 48pt;" align="right" width="64">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 21:30</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 21:40</td> <td style="width: 48pt;" align="right" width="64">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt; width: 86pt;" align="right" height="17" width="114">8/12/09 22:25</td> <td class="xl67" style="width: 80pt;" align="right" width="106">8/12/09 23:19</td> <td style="width: 48pt;" align="right" width="64">54</td> </tr> </tbody></table>
I'm at a loss for what to do to get the correct results here, especially because the number of cells overlapped and degree of overlap between two cells vary widely. Taking the bottom four rows as an example, adding the Minutes column gives 206 minutes, whereas I need to know the total time spent performing tasks - 141 minutes (20:58 through 23:19). Taking the earliest start time and last end time is insufficient because there are often several breaks between tasks.
Any help would be greatly appreciated.
