如何在MS EXCEL 里算时间加减 用哪个函数?

aaabbb

如何在MS EXCEL 里算时间加减 用哪个函数?

比如 10:30 到 17:40 是 7 小时另 10 分种 再用这个60进位时间数 乘以 小时工资数得一个日工资的总数?

先谢谢了

徒然客

直接加减就可以了

Bluejay

<p>如按照二楼所说会出现一个问题，时间相加值如果超过24:00，则数值归回到超出部分。比如25:00变成了1:00。</p><p>我这里有个方法，不过仅限于时间相减。比如时间（格式） 18:30&nbsp;减去 17:00，相减之和（格式）为 1.5 小时。见下图：</p><p><table cellspacing="0" cellpadding="0" width="226" border="0" style="WIDTH: 171pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="53" style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696;"></col><col></col><col width="29" style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 928;"></col><col></col><col width="53" style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696;"></col><col></col><col span="2" width="29" style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 928;"></col><col></col><col width="33" style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1056;"></col><col></col></colgroup><tbody><tr height="26" style="HEIGHT: 20.1pt; mso-height-source: userset;"><td class="xl26" width="53" height="26" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">19:00</font></td><td class="xl24" width="29" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">至</font></td><td class="xl27" width="53" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">20:30</font></td><td class="xl24" width="29" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">计</font></td><td class="xl28" width="29" xfmla="=IF(OR(A1=&quot;&quot;,C1=&quot;&quot;),&quot;&quot;,SUM(HOUR(SUM(C1,-A1)),MINUTE(SUM(C1,-A1))/60))" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 22pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="Arial" size="2">1.5</font></td><td class="xl25" width="33" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 25pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font size="2">小时</font></td></tr></tbody></table></p><p>其中 <strong>1.5</strong> 单元格处使用的函数为：</p><p>=IF(OR(B8="",D8=""),"",SUM(HOUR(SUM(D8,-B8)),MINUTE(SUM(D8,-B8))/60))</p><p>心得共享，祝好运！</p><p></p><p></p>

[此贴子已经被作者于2007-1-3 22:51:35编辑过]

萍丫头

直接加减做不到啊，我也试着做了啊

excelzzyzgr

,我倒啊!!!!![em10]