How to fill values into a column based on the 'Reference' column

J

jonglee

New Member
Joined
Aug 5, 2011
Messages
5
Hi All, I have some data like below, what I am trying to do is to round off the time on the third collumn (C), and fill in the neighboring column (fourth column, D) with the corresponding values from the second column (B). For example, C1 rounded-off is 11:20:00, then D1 should be 100; C2 rounded-off is 11:20:00, then D2 should be 100; C3 is 11:21:00, then D3 should be 110;... Thank you very much for your kind help.
A B C
<table width="340" border="0" cellpadding="0" cellspacing="0"><col style="width: 78pt;" width="104"> <col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 78pt;" width="104" align="right" height="17">11:20:00 AM</td> <td class="xl26" style="width: 48pt;" width="64" align="right">100.00</td> <td class="xl24" style="width: 81pt;" width="108" align="right">11:20:06 AM</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:21:00 AM</td> <td class="xl26" align="right">110.00</td> <td class="xl24" align="right">11:20:28 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:22:00 AM</td> <td class="xl26" align="right">120.00</td> <td class="xl24" align="right">11:20:42 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:23:00 AM</td> <td class="xl26" align="right">130.00</td> <td class="xl24" align="right">11:20:56 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:24:00 AM</td> <td class="xl26" align="right">140.00</td> <td class="xl24" align="right">11:21:32 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:25:00 AM</td> <td class="xl26" align="right">150.00</td> <td class="xl24" align="right">11:22:18 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:26:00 AM</td> <td class="xl26" align="right">160.00</td> <td class="xl24" align="right">11:22:30 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:27:00 AM</td> <td class="xl26" align="right">170.00</td> <td class="xl24" align="right">11:22:36 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:28:00 AM</td> <td class="xl26" align="right">180.00</td> <td class="xl24" align="right">11:22:55 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:23:06 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:23:34 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:05 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:20 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:40 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:07 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:29 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:35 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:00 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:24 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:47 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:02 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:06 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:33 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:50 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:28:04 AM</td> <td>
</td> </tr> </tbody></table>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Just to add a few words about the reason why I am doing this. The first two columns (time and value) are obtained from a data-logger not connected to the main equipment, while the third column (time) and its subsequent columns (not shown here) are recorded by the main equipment. Unfortunately, they cannot be synchronized, so I have to fill the values of the data-logger into the main equipment data. Hope now it is clearer ... Thank you.
 
Upvote 0
Use this in D column:
Code: 
=VLOOKUP(TIME(HOUR(A1),MINUTE(A1),IF(SECOND(A1)<30,0,60)),$A$1:$B$100,2,0)
 
Upvote 0
jonglee said:
Hi All, I have some data like below, what I am trying to do is to round off the time on the third collumn (C), and fill in the neighboring column (fourth column, D) with the corresponding values from the second column (B). For example, C1 rounded-off is 11:20:00, then D1 should be 100; C2 rounded-off is 11:20:00, then D2 should be 100; C3 is 11:21:00, then D3 should be 110;... Thank you very much for your kind help.
A B C
<TABLE cellSpacing=0 cellPadding=0 width=340 border=0><COLGROUP><COL style="WIDTH: 78pt" width=104><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 81pt" width=108><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="WIDTH: 78pt; HEIGHT: 12.75pt" align=right width=104 height=17>11:20:00 AM</TD><TD class=xl26 style="WIDTH: 48pt" align=right width=64>100.00</TD><TD class=xl24 style="WIDTH: 81pt" align=right width=108>11:20:06 AM</TD><TD style="WIDTH: 48pt" width=64>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:21:00 AM</TD><TD class=xl26 align=right>110.00</TD><TD class=xl24 align=right>11:20:28 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:22:00 AM</TD><TD class=xl26 align=right>120.00</TD><TD class=xl24 align=right>11:20:42 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:23:00 AM</TD><TD class=xl26 align=right>130.00</TD><TD class=xl24 align=right>11:20:56 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:24:00 AM</TD><TD class=xl26 align=right>140.00</TD><TD class=xl24 align=right>11:21:32 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:25:00 AM</TD><TD class=xl26 align=right>150.00</TD><TD class=xl24 align=right>11:22:18 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:26:00 AM</TD><TD class=xl26 align=right>160.00</TD><TD class=xl24 align=right>11:22:30 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:27:00 AM</TD><TD class=xl26 align=right>170.00</TD><TD class=xl24 align=right>11:22:36 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="HEIGHT: 12.75pt" align=right height=17>11:28:00 AM</TD><TD class=xl26 align=right>180.00</TD><TD class=xl24 align=right>11:22:55 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:23:06 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:23:34 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:24:05 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:24:20 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:24:40 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:25:07 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:25:29 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:25:35 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:26:00 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:26:24 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:26:47 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:27:02 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:27:06 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:27:33 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:27:50 AM</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl27>

</TD><TD class=xl24 align=right>11:28:04 AM</TD><TD>

</TD></TR></TBODY></TABLE>
Click to expand...
Try this...

Book1
ABCDE
211:20:00 AM10011:20:06 AM11:20:00 AM100
311:21:00 AM11011:20:28 AM11:20:00 AM100
411:22:00 AM12011:20:42 AM11:21:00 AM110
511:23:00 AM13011:20:56 AM11:21:00 AM110
611:24:00 AM14011:21:32 AM11:22:00 AM120
711:25:00 AM15011:22:18 AM11:22:00 AM120
811:26:00 AM16011:22:30 AM11:23:00 AM130
911:27:00 AM17011:22:36 AM11:23:00 AM130
1011:28:00 AM18011:22:55 AM11:23:00 AM130
11__11:23:06 AM11:23:00 AM130
12__11:23:34 AM11:24:00 AM140
13__11:24:05 AM11:24:00 AM140
14__11:24:20 AM11:24:00 AM140
15__11:24:40 AM11:25:00 AM150
16__11:25:07 AM11:25:00 AM150
17__11:25:29 AM11:25:00 AM150
18__11:25:35 AM11:26:00 AM160
19__11:26:00 AM11:26:00 AM160
20__11:26:24 AM11:26:00 AM160
21__11:26:47 AM11:27:00 AM170
22__11:27:02 AM11:27:00 AM170
23__11:27:06 AM11:27:00 AM170
24__11:27:33 AM11:28:00 AM180
25__11:27:50 AM11:28:00 AM180
26__11:28:04 AM11:28:00 AM180
Sheet1

This formula entered in D2:

=--TEXT(ROUND(C2*1440,0)/1440,"h:mm:ss am/pm")

This formula entered in E2:

=LOOKUP(D2,A$2:B$10)

Select both D2 and E2 and copy down as needed.
 
Upvote 0
Thank you, it works pretty well, just that the last variable should be 1 or TRUE, i.e. VLOOKUP(..............., $A$1:$B$100,2,1), instead of 0.

Thank you very much, again.:)

bebo021999 said:
Use this in D column:
Code: 
=VLOOKUP(TIME(HOUR(A1),MINUTE(A1),IF(SECOND(A1)<30,0,60)),$A$1:$B$100,2,0)
Click to expand...
 
Upvote 0
Thank you, Valko for your prompt help. It works perfectly.

I did not expect such a fast reply from you guys. This is a great place to learn and share skills.

T. Valko said:
Try this...

Sheet1

<table style="font-family: Verdana,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 92px;"><col style="width: 37px;"><col style="width: 92px;"><col style="width: 92px;"><col style="width: 37px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right; border: 1px solid rgb(0, 0, 0);">11:20:00 AM</td><td style="text-align: right; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">100</td><td style="text-align: right; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:06 AM</td><td style="text-align: right; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:00 AM</td><td style="text-align: right; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">100</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:21:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">110</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:28 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">100</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">120</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:42 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:21:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">110</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">130</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:20:56 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:21:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">110</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">140</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:21:32 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">120</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">150</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:18 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">120</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">160</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:30 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">130</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">170</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:36 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">130</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:28:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">180</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:22:55 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">130</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:06 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">130</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:23:34 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">140</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:05 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">140</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:20 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">140</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:24:40 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">150</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:07 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">150</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:29 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">150</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:25:35 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">160</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">160</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:24 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">160</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:26:47 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">170</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:02 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">170</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:06 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">170</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:33 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:28:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">180</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:27:50 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:28:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">180</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:28:04 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">11:28:00 AM</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">180</td></tr></tbody></table>


This formula entered in D2:

=--TEXT(ROUND(C2*1440,0)/1440,"h:mm:ss am/pm")

This formula entered in E2:

=LOOKUP(D2,A$2:B$10)

Select both D2 and E2 and copy down as needed.
Click to expand...
 
Upvote 0
Sorry, just realize the question was not complete.

The time column is composed of two parts: date and time, in the format of m/d/yy hh:mm:ss am/pm

So when filling in the values, it must match not only the hh:mm:ss, but also the date, i.e. m/d/yy.

<table style="width: 351px; height: 1801px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="123"> <col style="width: 48pt;" width="64"> <col style="width: 86pt;" width="115"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; width: 92pt;" width="123" align="right" height="17">7/20/11 11:20 AM</td> <td class="xl24" style="width: 48pt;" width="64" align="right">100.00</td> <td class="xl27" style="width: 86pt;" width="115" align="right">7/20/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:21 AM</td> <td class="xl24" align="right">110.00</td> <td class="xl27" align="right">7/20/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:22 AM</td> <td class="xl24" align="right">120.00</td> <td class="xl27" align="right">7/20/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:23 AM</td> <td class="xl24" align="right">130.00</td> <td class="xl27" align="right">7/20/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:24 AM</td> <td class="xl24" align="right">140.00</td> <td class="xl27" align="right">7/20/11 11:21 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:25 AM</td> <td class="xl24" align="right">150.00</td> <td class="xl27" align="right">7/20/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:26 AM</td> <td class="xl24" align="right">160.00</td> <td class="xl27" align="right">7/20/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:27 AM</td> <td class="xl24" align="right">170.00</td> <td class="xl27" align="right">7/20/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/20/11 11:28 AM</td> <td class="xl24" align="right">180.00</td> <td class="xl27" align="right">7/20/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:20 AM</td> <td class="xl24" align="right">200.00</td> <td class="xl27" align="right">7/20/11 11:23 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:21 AM</td> <td class="xl24" align="right">210.00</td> <td class="xl27" align="right">7/20/11 11:23 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:22 AM</td> <td class="xl24" align="right">220.00</td> <td class="xl27" align="right">7/20/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:23 AM</td> <td class="xl24" align="right">230.00</td> <td class="xl27" align="right">7/20/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:24 AM</td> <td class="xl24" align="right">240.00</td> <td class="xl27" align="right">7/20/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:25 AM</td> <td class="xl24" align="right">250.00</td> <td class="xl27" align="right">7/20/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:26 AM</td> <td class="xl24" align="right">260.00</td> <td class="xl27" align="right">7/20/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:27 AM</td> <td class="xl24" align="right">270.00</td> <td class="xl27" align="right">7/20/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" align="right" height="17">7/21/11 11:28 AM</td> <td class="xl24" align="right">280.00</td> <td class="xl27" align="right">7/20/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/20/11 11:28 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:20 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:21 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:22 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:23 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:23 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:24 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:25 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:26 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:27 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17">
</td> <td class="xl25">
</td> <td class="xl27" align="right">7/21/11 11:28 AM</td> </tr> </tbody></table>

So in this case, what would be the solution? Thanks again.

jonglee said:
Hi All, I have some data like below, what I am trying to do is to round off the time on the third collumn (C), and fill in the neighboring column (fourth column, D) with the corresponding values from the second column (B). For example, C1 rounded-off is 11:20:00, then D1 should be 100; C2 rounded-off is 11:20:00, then D2 should be 100; C3 is 11:21:00, then D3 should be 110;... Thank you very much for your kind help.
A B C
<table width="340" border="0" cellpadding="0" cellspacing="0"><col style="width: 78pt;" width="104"> <col style="width: 48pt;" width="64"> <col style="width: 81pt;" width="108"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 78pt;" width="104" align="right" height="17">11:20:00 AM</td> <td class="xl26" style="width: 48pt;" width="64" align="right">100.00</td> <td class="xl24" style="width: 81pt;" width="108" align="right">11:20:06 AM</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:21:00 AM</td> <td class="xl26" align="right">110.00</td> <td class="xl24" align="right">11:20:28 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:22:00 AM</td> <td class="xl26" align="right">120.00</td> <td class="xl24" align="right">11:20:42 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:23:00 AM</td> <td class="xl26" align="right">130.00</td> <td class="xl24" align="right">11:20:56 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:24:00 AM</td> <td class="xl26" align="right">140.00</td> <td class="xl24" align="right">11:21:32 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:25:00 AM</td> <td class="xl26" align="right">150.00</td> <td class="xl24" align="right">11:22:18 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:26:00 AM</td> <td class="xl26" align="right">160.00</td> <td class="xl24" align="right">11:22:30 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:27:00 AM</td> <td class="xl26" align="right">170.00</td> <td class="xl24" align="right">11:22:36 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" align="right" height="17">11:28:00 AM</td> <td class="xl26" align="right">180.00</td> <td class="xl24" align="right">11:22:55 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:23:06 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:23:34 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:05 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:20 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:24:40 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:07 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:29 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:25:35 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:00 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:24 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:26:47 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:02 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:06 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:33 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:27:50 AM</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">
</td> <td class="xl27">
</td> <td class="xl24" align="right">11:28:04 AM</td> <td>
</td> </tr> </tbody></table>
Click to expand...
 
Upvote 0
jonglee said:
Sorry, just realize the question was not complete.

The time column is composed of two parts: date and time, in the format of m/d/yy hh:mm:ss am/pm

So when filling in the values, it must match not only the hh:mm:ss, but also the date, i.e. m/d/yy.

<TABLE style="WIDTH: 351px; HEIGHT: 1801px" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="WIDTH: 92pt" width=123><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 86pt" width=115><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="WIDTH: 92pt; HEIGHT: 12.75pt" align=right width=123 height=17>7/20/11 11:20 AM</TD><TD class=xl24 style="WIDTH: 48pt" align=right width=64>100.00</TD><TD class=xl27 style="WIDTH: 86pt" align=right width=115>7/20/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:21 AM</TD><TD class=xl24 align=right>110.00</TD><TD class=xl27 align=right>7/20/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:22 AM</TD><TD class=xl24 align=right>120.00</TD><TD class=xl27 align=right>7/20/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:23 AM</TD><TD class=xl24 align=right>130.00</TD><TD class=xl27 align=right>7/20/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:24 AM</TD><TD class=xl24 align=right>140.00</TD><TD class=xl27 align=right>7/20/11 11:21 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:25 AM</TD><TD class=xl24 align=right>150.00</TD><TD class=xl27 align=right>7/20/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:26 AM</TD><TD class=xl24 align=right>160.00</TD><TD class=xl27 align=right>7/20/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:27 AM</TD><TD class=xl24 align=right>170.00</TD><TD class=xl27 align=right>7/20/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/20/11 11:28 AM</TD><TD class=xl24 align=right>180.00</TD><TD class=xl27 align=right>7/20/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:20 AM</TD><TD class=xl24 align=right>200.00</TD><TD class=xl27 align=right>7/20/11 11:23 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:21 AM</TD><TD class=xl24 align=right>210.00</TD><TD class=xl27 align=right>7/20/11 11:23 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:22 AM</TD><TD class=xl24 align=right>220.00</TD><TD class=xl27 align=right>7/20/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:23 AM</TD><TD class=xl24 align=right>230.00</TD><TD class=xl27 align=right>7/20/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:24 AM</TD><TD class=xl24 align=right>240.00</TD><TD class=xl27 align=right>7/20/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:25 AM</TD><TD class=xl24 align=right>250.00</TD><TD class=xl27 align=right>7/20/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:26 AM</TD><TD class=xl24 align=right>260.00</TD><TD class=xl27 align=right>7/20/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:27 AM</TD><TD class=xl24 align=right>270.00</TD><TD class=xl27 align=right>7/20/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" align=right height=17>7/21/11 11:28 AM</TD><TD class=xl24 align=right>280.00</TD><TD class=xl27 align=right>7/20/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/20/11 11:28 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:20 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:21 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:22 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:23 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:23 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:24 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:25 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:26 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:27 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="HEIGHT: 12.75pt" height=17>

</TD><TD class=xl25>

</TD><TD class=xl27 align=right>7/21/11 11:28 AM</TD></TR></TBODY></TABLE>

So in this case, what would be the solution? Thanks again.
Click to expand...
Not sure...

Maybe change the formula in D2 to:

=INT(C2)+TEXT(ROUND(C2*1440,0)/1440,"h:mm:ss am/pm")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
111
Peter_SSs
Peter_SSs
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
125
Kaper
Kaper
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
396
dshtesy
D

Forum statistics

Threads
1,224,551
Messages
6,179,476
Members
452,915
Latest member
hannnahheileen

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