• 2

EXCEL公式問題請教:如何算出時間差?

請問一下各位Excel專家:

小弟正在做一份工作時數統計報表,格式如下:
A1:上班時間
B1:下班時間
C1:工作時數

需求1:求得A1~B1之間經過多少小時(以 時 為單位,求到小數第二位)。
需求2:時間格式(以10點30分跟22點30分為例):格式為1030、2230。
需求3:C1必須可以做數學運算,因為要乘以時薪。

小弟目前是有想出一個公式(如下),原以為此公式可行,不料實作後,填入一個員工的上下班時間,例如【A1:1030】,【B1:2100】,卻得到11.17小時數值,正確應為10.5,即時發現計算有誤,故而暫停使用!
=INT((B1-A1)/100)+(((B1-A1)/100)-TRUNC((B1-A1)/100))*100/60)

請各位大大不吝指教 謝謝大家。

PS:小弟使用EXCEL 2003版

2016-05-24 16:19 發佈
如圖:



1.A151 為自訂格式"hhmm",但必須先設為制式的時間格式,再改為自訂格式

2.B151 為自訂格式"hhmm",但必須先設為制式的時間格式,再改為自訂格式

3.C151 公式為"=HOUR(B151-A151)+ ROUND(MINUTE(B151-A151)/60,2)",格式設為標準的數值格式即可

提供另一種方式供參考,
雖然公式較長,但無需另外設定 A1 & B1 的欄位格式!

註:公式未考慮跨日的情況!





=MROUND((TEXT(LEFT(B1,LEN(B1)-2)&":"&RIGHT(B1,2),"hh:mm")-TEXT(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2),"hh:mm"))*24, 0.01)
My Interior Knowledge is Extraordinaire
antonioChang wrote:
如圖:1.A151...(恕刪)


Hello 大大
小弟試了您的公式,但不知您所謂的「必須先設為制式的時間格式,再改為自訂格式」
是要先改成哪一個制式的時間格式? 因為我改了以後,keyin的時間卻變成00:00
Mystique Hsiao wrote:
提供另一種方式供參...(恕刪)


Hello 大大
您的公式我改了以後,結果變成 #NAME? 不知是哪裡的問題呢?

mikro wrote:
您的公式我改了以後,結果變成 #NAME? 不知是哪裡的問題呢?


請先確認輸入的公式內容是否正確(可貼圖上來看看)!
My Interior Knowledge is Extraordinaire
=MOD(TEXT(B1,"00!:00")-TEXT(A1,"00!:00"),1)*24
(至於小數點,看是用自訂格式,還是用函數,都可以)


請參考,我以前回答的…稍微多翻幾頁就可以看到了

「問題」EXCEL如何計算時間
http://www.mobile01.com/topicdetail.php?f=511&t=3879736&p=1

EXCEL困難的上班時間計算
http://www.mobile01.com/topicdetail.php?f=511&t=3883510


像這樣,自訂數值格式為"hhmm":


mikro wrote:
請問一下各位Excel...(恕刪)


=INT((B1-A1)/100)+(((B1-A1)/100)-TRUNC((B1-A1)/100))*100/60)

分是60進位,不能先加減再換算成小時,要先換算成小時再加減

=INT(B1/100)+((B1/100)-TRUNC(B1/100))*100/60-(INT(A1/100)+((A1/100)-TRUNC(A1/100))*100/60)

簡化
=((INT(B1/100)+MOD(B1,100)/60)-(INT(A1/100)+MOD(A1,100)/60))

snare wrote:
=MOD(TEXT...(恕刪)


謝謝 snare 大大的教學
小弟有去看過您另一篇回文
=TEXT(MOD(TEXT(B1,"00!:00")-TEXT(A1,"00!:00"),1),"[m]")
果真有試出結果,但因為是要以「時」的單位顯示,所以我突發其想,[m]應表示分鐘,因此我將[m]改為[h],結果的確有變成時數,但因為時數要算到小數第2位,所以就無從下手了..

對了,忘記補充...
大大您新給的這道公式
=MOD(TEXT(B1,"00!:00")-TEXT(A1,"00!:00"),1)*24
小弟果然試成功了!!
因為小弟工作上的關係,時常需要做Excel報表,所以特想了解一下,您這公式的意義為何?
主要不太了解"00!:00" 這個格式對於公式的作用為何? 以及最後為何要加 ,1
方便的話還請大大指導一下,感謝你 ^^
  • 2
內文搜尋
X
評分
評分
複製連結
Mobile01提醒您
您目前瀏覽的是行動版網頁
是否切換到電腦版網頁呢?