Time formula

[shansakhi]

Hi to All,

Need one help from you.

If I want to convert below digits to TIME format and to do their addion.

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 144pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=192 colSpan=3 height=18>Serial Nbrs</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=64>TIME</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>310</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>530</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(A2:B2)">840</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.3611111111111111">8:40</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18 x:num>855</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num>530</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(A3:B3)">1385</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.60069444444444442">14:25</TD></TR></TBODY></TABLE>

Third coloum is sum of Coloum One and Two.

Please assist.

Thanks

 

[DonkeyOte]

You have a couple of options but in part it will depend on how you record 0-9 minutes where hour > 0 ... ie 209 or 29 ?

Couple of options for you below

D is based off value in C
E calculates based off A & B directly (ignores C)

I would opt for the latter approach myself but again you will need to specify how you handle 2hr 9min etc... (ie if no leading zeroes for minutes these will not work)

Excel Workbook
	A	B	C	D	E
1	310	530	840	08:40	08:40
2	855	530	1385	14:25	14:25
3	123	209	332	03:32	03:32
Sheet1

Should add this is also based on the assumption that C (SUM(A:B)) < 24 hours...

 

[theozz]

Hi ! Here Suggest to you

<table style="border-collapse: collapse;" 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="4" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td></td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr></tbody></table></td><td width="234"><table colspan="3" rowspan="1" bgcolor="#939393" border="0" cellspacing="1" width="234"><col width="78"><col width="78"><col width="78"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">C</td></tr><tr height="18"><td colspan="2" align="center" bgcolor="#ffffff">Numeric</td><td align="left" bgcolor="#ffffff">Time value</td></tr></tbody></table><table colspan="3" rowspan="2" bgcolor="#939393" border="0" cellspacing="1" width="234"><col width="78"><col width="78"><col width="78"><tbody bgcolor="#ffffff"><tr height="18"><td align="right" bgcolor="#ffffff">310</td><td align="right" bgcolor="#ffffff">530</td><td align="right" bgcolor="#ccffcc">8:40</td></tr><tr height="18"><td align="right" bgcolor="#ffffff">855</td><td align="right" bgcolor="#ffffff">530</td><td align="right" bgcolor="#ccffcc">14:25</td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="3" height="54"></colspan=5><table style="border-collapse: collapse;" 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">C2</td><td>=SUMPRODUCT(--(TEXT(A2:B2,"00"":""00")&":00"))</td><td align="right">8:40</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=SUMPRODUCT(--(TEXT(A2:B2,\"00\"\":\"\"00\")&\":00\"))");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center"></td><td>C2 His Formula Used This Cell -> C2:C3</td><td align="left"></td><td></td></tr><tr height="24"><td colspan="5">
How about this suggest?
</td></tr></tbody></table>

 

[DonkeyOte]

Very nice (again) theozz... and better than mine for multiple reasons -- can handle 24+ hrs etc...

 

[barry houdini]

I'd suggest just

=TEXT(A2,"00\:00")+TEXT(B2,"00\:00")