Number of occurrences in time intervals

S

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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Group number of purchased SKUs by counting customer
2
Replies
16
Views
230
shaowu459
shaowu459
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
151
dilshod_k
D
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
302
Zee996
Z
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
104
strugglingaccountant
S
G
  • Question
Help me with this!
Replies
1
Views
179
bobaol
B

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

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

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

Disable uBlock

Follow these easy steps to disable uBlock

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