or

or

## 如何在Excel中計算工作時間和減去午餐時間？

 添加一些年/月/日/週/小時/分鐘/秒的日期： Kutools for Excel“ 公式 實用程序包含一些常用公式，例如添加日期，基於生日計算年齡等。 用這些通常的公式，你可以很快得到你想要的結果，而不用記住任何公式。 閱讀更多關於此功能的信息 Kutools for Excel：比200方便的Excel加載項，可以在60天免費試用。

#### 用公式計算工作時間和減去午餐時間

###### 驚人！ 在Excel中使用高效標籤，如Chrome，Firefox和Safari！

1。 輸入這個公式： =SUM((C2-B2)+(E2-D2))*24 進入您的時間記錄單元旁邊的空白單元格， F2例如，看截圖：

2。 然後將填充手柄向下拖動到您想要應用此公式的單元格中，並且不包括午餐時間的所有總小時數都將立即計算出來。 看截圖：

### 推薦的生產力工具

#### Kutools for Excel

300 Excel的新功能，讓Excel變得簡單而強大：

• 合併單元格/行/列而不丟失數據。
• 合併和合併多個工作表和工作簿。
• 比較範圍，複製多個範圍，將文本轉換為日期，單位和貨幣轉換。
• 按顏色計算，分頁小計，高級分類和超級篩選，
• 更多選擇/插入/刪除/文本/格式/鏈接/評論/工作簿/工作表工具...

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
• To post as a guest, your comment is unpublished.
· 7 months ago
@Sheena:

Found something laying around that actually solves your question.

Columns D, E and F are the Start time, End Time and Break.

Have a table somewhere that explains the breaks that have to be calculated:

from till Pause
0:00 6:00 0:00
6:00 9:00 0:30
9:00 10:00 0:45

Then use the following expresssion (note that my reference to the table are on a tab called Anhang):

=IF(AND(D18=ʺʺ,E18=ʺʺ),ʺʺ,(E18+(D18>E18)-D18)-IF((E18+(D18>E18)-D18)>Anhang!\$A\$6,IF(F18>Anhang!\$C\$6,F18,Anhang!\$C\$6),IF((E18+(D18>E18)-D18)<=Anhang!\$B\$4,IF(F18>Anhang!\$C\$4,F18,Anhang!\$C\$4),IF(F18>Anhang!\$C\$5,F18,Anhang!\$C\$5))))
• To post as a guest, your comment is unpublished.
· 9 months ago
I need help with a formula. So I'm creating schedules, I have a log in and log out time. If that time is over 6 hours there has to be a 30 minute lunch. I want a formula that will calculate that time worked including a lunch IF it's over 6 hours
• To post as a guest, your comment is unpublished.
· 8 months ago
I too would like the answer to this exact situation!
• To post as a guest, your comment is unpublished.
· 8 months ago
Did you get help on this question?
• To post as a guest, your comment is unpublished.
· 8 months ago
I would also like the answer to this. Can it be varied? Example: 12 hour shift workers get an hour break, as do 11 and 10 hour shit workers. However, if you work 8 or 9 hours you get 45 minutes deducted, 6 - 7 hours have 30 minutes deducted and 5 hours have 15 minutes deducted. Any hours worked below 5 do not incur a break.