Number of occurrences in time intervals

[SoulSeeker]

Good day,

I'm currently needing a formula to count the number of occurrences in a time interval, let's say i'm keeping a log of the time a person went through a door and the values are as follow:
<table border="0" cellpadding="0" cellspacing="0" width="238" height="230"><tbody><tr><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">John</td> <td class="xl63" style="width: 48pt;" align="right" width="64">9:36 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">John</td> <td class="xl63" align="right">9:47 AM
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Thomas</td> <td class="xl63" align="right">10:47 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Nataly</td> <td class="xl63" align="right">10:48 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ashlie</td> <td class="xl63" align="right">10:49 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Thomas</td> <td class="xl63" align="right">10:50 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rick</td> <td class="xl63" align="right">11:50 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">James</td> <td class="xl63" align="right">12:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Tonya</td> <td class="xl63" align="right">1:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Kelvin</td> <td class="xl63" align="right">2:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Cindy</td> <td class="xl63" align="right">3:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Jenny</td> <td class="xl63" align="right">4:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Chris</td> <td class="xl63" align="right">5:50 PM</td> </tr> </tbody></table>
On another table i want to know how many times a person went through the door on a given interval:
0900 - 1000 1000 - 1100 1200 - 1300 Etc
John 2 0 0
Thomas 2
Etc

Any help will be greatly appreciated. Thanks

 

[SoulSeeker]

Here's another example of exactly what i need, (I need a formula for the values in red):

<table border="1" cellpadding="0" cellspacing="0" width="245"><col style="width: 16pt;" width="21"> <col style="width: 46pt;" width="61" span="3"> <col style="width: 31pt;" width="41"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 16pt; word-wrap: break-word;" width="21" height="20"></td> <td class="xl67" style="border-left: medium none; width: 46pt;" width="61">A</td> <td class="xl67" style="border-left: medium none; width: 46pt;" width="61">B</td> <td class="xl67" style="border-left: medium none; width: 46pt;" width="61">C</td> <td class="xl67" style="border-left: medium none; width: 31pt;" width="41">D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Time Log</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Start</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">End</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Count</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">9:36 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">9:00 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:00 AM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">9:47 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:00 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">11:00 AM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:47 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">11:00 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">12:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:48 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">12:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:49 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:50 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">11:50 AM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">9</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">12:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">10</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">6:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">11</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">6:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">7:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">12</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">3:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">7:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">8:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">13</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">4:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">8:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">9:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">14</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5:50 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">9:00 PM</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">10:00 PM</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0
</td> </tr> </tbody></table>

 

[mikecroom]

If I understand you correctly this might be a good tasks for countif
You'll need Excel 2007 or 2010

In a table of user name and time period, each cell can have a countif of the raw data

You will need 3 conditions:
1 to match the user name
2 to check the time in the raw data is after the start of the interval
3 to check the time in the raw data is before the end of the interval

My advice is to keep the raw data and the summary well apart.

Mike

 

[SoulSeeker]

That is correct Mike, let me do it again:

<table border="1" cellpadding="0" cellspacing="0" width="152"><col style="width: 16pt;" width="21"> <col style="width: 41pt;" width="55"> <col style="width: 57pt;" width="76"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 16pt; word-wrap: break-word;" width="21" height="20"></td> <td class="xl68" style="border-left: medium none; width: 41pt;" width="55">A</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Name</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Time Log</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">9:36 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">9:47 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Thomas</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">10:47 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Nataly</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">10:48 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Ashlie</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">10:49 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Thomas</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">10:50 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Rick</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">11:50 AM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">9</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">12:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">10</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Thomas</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">1:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">11</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">2:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">12</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Thomas</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">3:25 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">13</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">3:50 PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">14</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Chris</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">4:50 PM</td> </tr> </tbody></table>
So the report would be like this, and the numbers in red is what i'm needing the formula for:

<table border="1" cellpadding="0" cellspacing="0" width="684"><col style="width: 16pt;" width="21"> <col style="width: 41pt;" width="55"> <col style="width: 57pt;" width="76" span="8"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 16pt; word-wrap: break-word;" width="21" height="20"></td> <td class="xl65" style="border-left: medium none; width: 41pt;" width="55">A</td> <td class="xl67" style="border-left: medium none; width: 57pt;" width="76">B</td> <td class="xl67" style="border-left: medium none; width: 57pt;" width="76">C</td> <td class="xl67" style="border-left: medium none; width: 57pt;" width="76">D</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">E</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">F</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">G</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">H</td> <td class="xl68" style="border-left: medium none; width: 57pt;" width="76">I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Name</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">0900 - 1000</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1000 - 1100</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1100 - 1200</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1200 - 1300</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1300 - 1400</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1400 - 1500</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1500 - 1600</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1600 - 1700</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Thomas</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Nataly</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Ashlie</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Rick</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Chris</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl66" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;"></td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00
2	John	2					1	1
3	Thomas		2			1		1
4	Nataly		1
5	Ashlie		1
6	Rick			1
7	James				1
8	Chris								1
Sheet4

this will return the same as you have, Formula in B2 dragged across and down. Your times at the column header i've had to alter, there's a way to hard code the times based on your set up but it would make for a big formula. the 17:00 time can be placed/hiden anywhere. If you want the times back as you had them and the formula to include them as you have them let me know, I'll come back to it later

 

[scottylad2]

this is the formula leaving your times the column headers

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	0900 - 1000	1000 - 1100	1100 - 1200	1200 - 1300	1300 - 1400	1400 - 1500	1500 - 1600	1600 - 1700
2	John	2					1	1
3	Thomas		2			1		1
4	Nataly		1
5	Ashlie		1
6	Rick			1
7	James				1
8	Chris								1
Sheet4

 

[Haseeb Avarakkan]

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Time Log</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Name</td><td style=";">0900 - 1000</td><td style=";">1000 - 1100</td><td style=";">1100 - 1200</td><td style=";">1200 - 1300</td><td style=";">1300 - 1400</td><td style=";">1400 - 1500</td><td style=";">1500 - 1600</td><td style=";">1600 - 1700</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">John</td><td style="text-align: right;;">9:36 AM</td><td style="text-align: right;;"></td><td style=";">John</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">John</td><td style="text-align: right;;">9:47 AM</td><td style="text-align: right;;"></td><td style=";">Thomas</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Thomas</td><td style="text-align: right;;">10:47 AM</td><td style="text-align: right;;"></td><td style=";">Nataly</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Nataly</td><td style="text-align: right;;">10:48 AM</td><td style="text-align: right;;"></td><td style=";">Ashlie</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Ashlie</td><td style="text-align: right;;">10:49 AM</td><td style="text-align: right;;"></td><td style=";">Rick</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Thomas</td><td style="text-align: right;;">10:50 AM</td><td style="text-align: right;;"></td><td style=";">James</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Rick</td><td style="text-align: right;;">11:50 AM</td><td style="text-align: right;;"></td><td style=";">Chris</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">James</td><td style="text-align: right;;">12:50 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Thomas</td><td style="text-align: right;;">1:50 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">John</td><td style="text-align: right;;">2:50 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Thomas</td><td style="text-align: right;;">3:25 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">John</td><td style="text-align: right;;">3:50 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Chris</td><td style="text-align: right;;">4:50 PM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A$2:$A$14,$D2,$B$2:$B$14,"<="&TEXT(<font color="Red">RIGHT(<font color="Green">E$1,4</font>),"00\:00"</font>)</font>)-SUM(<font color="Blue">$D2:D2</font>)</td></tr></tbody></table></td></tr></table><br />
Change the cell format to

General;;

If you are using 2003-, replace the formula with below,

=SUMPRODUCT(--($A$2:$A$14=$D2),--($B$2:$B$14<=TEXT(RIGHT(E$1,4),"00\:00")+0))-SUM($D2:D2)