Comparing and then counting times to their nearest 15min interval.

[Legacy 169354]

Hi,

I need to count cells with variable times (added by user in colmn 'G') and if they are not in the 15minute intervals add them to the closest 15minute interval (column B 'instance').
Times are in 24hr format.

E.g:

Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 75px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: center; font-weight: bold;">Time</td> <td style="font-weight: bold;">instance</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center; font-weight: bold;">Date</td> <td style="text-align: center; font-weight: bold;">time</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-weight: bold;">00:00</td> <td style="text-align: right;">1</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">22/03/2011</td> <td style="text-align: center;">00:00</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-weight: bold;">00:15</td> <td style="text-align: right;">3</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">23/03/2011</td> <td style="text-align: center;">00:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-weight: bold;">00:30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">24/03/2011</td> <td style="text-align: center;">00:30</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-weight: bold;">00:45</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">25/03/2011</td> <td style="text-align: center;">00:45</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-weight: bold;">01:00</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">26/03/2011</td> <td style="text-align: center;">01:00</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-weight: bold;">01:15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">27/03/2011</td> <td style="text-align: center;">01:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-weight: bold;">01:30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">28/03/2011</td> <td style="text-align: center;">00:13</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-weight: bold;">01:45</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">29/03/2011</td> <td style="text-align: center;">01:45</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: center; font-weight: bold;">02:00</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">30/03/2011</td> <td style="text-align: center;">02:50</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="text-align: center; font-weight: bold;">02:15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">31/03/2011</td> <td style="text-align: center;">02:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="text-align: center; font-weight: bold;">02:30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">1/04/2011</td> <td style="text-align: center;">02:30</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="text-align: center; font-weight: bold;">02:45</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">2/04/2011</td> <td style="text-align: center;">01:07</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="text-align: center; font-weight: bold;">03:00</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">3/04/2011</td> <td style="text-align: center;">03:00</td></tr> <tr style="height: 19px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; font-weight: bold;">03:15</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">4/04/2011</td> <td style="text-align: center;">03:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">5/04/2011</td> <td style="text-align: center;">02:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">6/04/2011</td> <td style="text-align: center;">02:30</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">7/04/2011</td> <td style="text-align: center;">01:07</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">8/04/2011</td> <td style="text-align: center;">03:00</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">9/04/2011</td> <td style="text-align: center;">08:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">10/04/2011</td> <td style="text-align: center;">09:15</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: center;">11/04/2011</td> <td style="text-align: center;">10:15</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

I have hundreds of date and time rows.

Column B(Instance) is where I will want the count to go for the Date and time entries (F and G).
I am having trouble using time in comparison/count formula's!
I am using Excel 2003.

Thanks in Advance!!!!

 

[T. Valko]

Thanks very much guys

You're welcome!