round to nearest 5 minutes

bam12 · Nov 15, 2008

i need to do a calculations of time and round to nearest 5 minutes

example 16:16- 15:00 = 1:16, but want to round it to 1:15
example 15:48- 15:00 = :48 want this to round to 00:50

6 or 7 to run down
8 or 9 to run up

thanks in advance for any help

barry houdini · Nov 15, 2008

Try like this

=ROUND((B1-A1)*288,0)/288

VoG · Nov 15, 2008

Try

=ROUND((A1-B1)*288,0)/288

where A1 is the later time. Format the result cell as Time.

theozz · Nov 15, 2008

Hi ! Here Suggest to you

<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.
DataSheet= Sheet1</td></tr><tr><td align="right" width="30"><table rowspan="6" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td>
</td></tr><tr height="19"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="19"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="19"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="19"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="19"><td align="right" bgcolor="#dcdcdc">5 </td></tr></tbody></table></td><td width="374"><table colspan="4" rowspan="2" bgcolor="#939393" border="0" cellspacing="1" width="374"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">C</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="93">D</td></tr><tr height="19"><td colspan="1" align="right" bgcolor="#ffffff" width="93">16:16</td><td colspan="1" align="right" bgcolor="#ffffff" width="93">15:00</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">1:16</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">1:15</td></tr><tr height="19"><td colspan="1" align="right" bgcolor="#ffffff" width="93">15:48</td><td colspan="1" align="right" bgcolor="#ffffff" width="93">15:00</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">0:48</td><td colspan="1" align="right" bgcolor="#ccffcc" width="93">0:50</td></tr></tbody></table><table colspan="4" rowspan="3" bgcolor="#939393" border="0" cellspacing="1" width="374"><tbody bgcolor="#ffffff"><tr height="19"><td colspan="4" align="left" bgcolor="#ffffff" width="374">Cause 1 is one day</td></tr><tr height="19"><td colspan="4" align="left" bgcolor="#ffffff" width="374">so time * 24 = one day</td></tr><tr height="19"><td colspan="4" align="left" bgcolor="#ffffff" width="374">by 5min so *60/5 and round result return</td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="5" height="90"></colspan=5><table style="border-collapse: separate;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="5" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center" bgcolor="#ccffcc">C1</td><td bgcolor="#ccffcc">=A1-B1</td><td align="right">1:16</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=A1-B1");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center" bgcolor=""> </td><td>C1 His Formula Used This Cell -> C1:C2</td><td align="left"> </td><td> </td></tr><tr height="20"><td align="center">3</td><td align="center" bgcolor="#ccffcc">D1</td><td bgcolor="#ccffcc">=ROUND(C1*24*60/5,0)*5/60/24</td><td align="right">1:15</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=ROUND(C1*24*60/5,0)*5/60/24");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center" bgcolor=""> </td><td>D1 His Formula Used This Cell -> D1:D2</td><td align="left"> </td><td> </td></tr><tr height="24"><td colspan="5">
How about this suggest?
</td></tr></tbody></table>

barry houdini · Nov 15, 2008

Another way, if you don't mind using Analysis ToolPak function, is

=MROUND(B1-A1,"0:05")

bam12 · Nov 22, 2008

need more now

I need to take the result of the time calculation after rounded to nearest 5 minutes and compare the minute field to a table and place that field onto spreadsheet

example 1 hr 10 minutes will now need to be 1 in one cell and .17 in another cell

table

5 equals .08
10 equals .17
15 equals .25
etc

thanks in advance for any help

VoG · Nov 22, 2008

Like this?

<b>Sheet10</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:25px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; 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><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">14:13</td><td style="text-align:right; ">12:04</td><td style="text-align:right; ">02:10</td><td style="text-align:right; ">0.25</td><td > </td><td style="text-align:right; ">0</td><td style="text-align:right; ">0.08</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">0.17</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10</td><td style="text-align:right; ">0.25</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">15</td><td style="text-align:right; ">0.34</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">20</td><td style="text-align:right; ">0.42</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">25</td><td style="text-align:right; ">0.51</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">30</td><td style="text-align:right; ">0.60</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">35</td><td style="text-align:right; ">0.68</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">40</td><td style="text-align:right; ">0.76</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">45</td><td style="text-align:right; ">0.85</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">50</td><td style="text-align:right; ">0.93</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">55</td><td style="text-align:right; ">1.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=ROUND(<span style=' color:008000; '>(A1-B1)</span>*288,0)/288</td></tr><tr><td >D1</td><td >=INDEX(G1:G12,MATCH<span style=' color:008000; '>(MINUTE<span style=' color:#0000ff; '>(C1)</span>,F1:F12,0)</span>)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Scott Huish · Nov 22, 2008

Assuming rounded time in C1:

To get the hour
=Hour(C1)

To get the minute in decimal:
=ROUND(MINUTE(C1)/60,2)

Copy down as necessary.

Excel Workbook

C

D

E

1