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 :)
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,917
Members
414,416
Latest member
Nobu

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