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.
· 6 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.
· 8 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.
· 7 months ago
I too would like the answer to this exact situation!
• To post as a guest, your comment is unpublished.
· 7 months ago
Did you get help on this question?
• To post as a guest, your comment is unpublished.
· 7 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.