Excel时间比较和减法 [英] Excel Time Comparison and Subtraction

问题描述

我正在尝试在30分钟内完成一次时间减去，并且我遇到了减速带.因此，我的表格如下.

I am trying to do a time subtraction in excel of 30 minutes and I am running into a speed bump. So the table I have are as follows.

Table "Schedule"
Column 1 is day of the week (Mon-Sun) (formated as general, as this is plain text)
Column 2 is start time of the shift (formated as h:mm AM/PM)
Column 3 is end time of the shift (formated as h:mm AM/PM)
Column 4 is duration of the shift (start to end) (formated by formula (TEXT(col3-col2,"h:mm")) )
Column 5 is paid hours (if the total hours is over 6.5 then subtract 0.5 hours for an unpaid lunch) (formula IF(col5>"6:30",col5-"0:30",D5) )

问题是，超过10个小时的分配开始到结束时(第4列，持续时间达到10个小时)，根本不减去午餐.

The issue is any time allotment over 10 hours start to end (where column 4, the duration hits 10 hours) no lunch is subtracted at all.

所以...开始时间9:00 AM，结束时间6:59 PM，总时数9:59，已付费时数9:29

So... Start 9:00 AM, End 6:59 PM, Hours Total 9:59, Hours Paid 9:29

但是...开始时间9:00 AM，结束时间7:00 PM，总计小时10:00，已付费小时10:00

But... Start 9:00 AM, End 7:00 PM, Hours Total 10:00, Hours Paid 10:00

那显然不应该发生.我在Google上找不到任何内容，因此我认为这里的excel专家可能会提供一些建议.

and that should obviously not happen. I can't find anything on google so I figured the excel gurus here may have some advice.

谢谢！

推荐答案

如果您的时间列是使用excel专用时间格式存储的，则应该很简单.混合数据类型可能是您遇到的问题.

If your time columns are stores using excel's dedicated time format, this should be straightforward. Mixed data types are likely your problem.

首先，请确保您使用时间功能(即

First, be sure your time columns (columns 2 and 3) are set using the time function, i.e.,

=时间(小时，分钟，秒)

=time(hours,minutes,seconds)

然后，您应该可以轻松地进行加法和减法.

Then, you should be able to add and subtract easily.

第4列:=第3列-第2列

Column 4: = column 3 - column 2

...然后还使用time()函数减去30分钟:

... then subtract 30 minutes also using the time() function:

第5列:= if(第4列>时间(6,30,0)，第4列-时间(0,30,0)，第4列)

Column 5: = if(column 4 > time(6,30,0),column 4 -time(0,30,0),column 4)

这篇关于Excel时间比较和减法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！