Number of occurrences in time intervals

SoulSeeker

New Member
Joined
Feb 26, 2011
Messages
12
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 :)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

SoulSeeker

New Member
Joined
Feb 26, 2011
Messages
12
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

New Member
Joined
Oct 8, 2010
Messages
47
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

New Member
Joined
Feb 26, 2011
Messages
12
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

Excel Workbook
ABCDEFGHIJ
1Name09:0010:0011:0012:0013:0014:0015:0016:0017:00
2John211
3Thomas211
4Nataly1
5Ashlie1
6Rick1
7James1
8Chris1
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

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
this is the formula leaving your times the column headers
Excel Workbook
ABCDEFGHI
1Name0900 - 10001000 - 11001100 - 12001200 - 13001300 - 14001400 - 15001500 - 16001600 - 1700
2John211
3Thomas211
4Nataly1
5Ashlie1
6Rick1
7James1
8Chris1
Sheet4
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,784
Members
430,568
Latest member
bortey

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
Top