如何在excel中计算时数

Question

我有以下格式的xls文件

Name    1              2             3            4
John    09:00-21:00                  09:00-21:00
Amy                    21:00-09:00                09:00-21:00

1,2,3,4等代表当月的天数，09:00-21:00 -工作时间。

我想根据下列条件计算工资：

09:00-21:00 - 10$/hour
21:00-00:00 - 15$/hour
00:00-03:00 - 20$/hour
etc.

依此类推(每小时可以有自己的成本，例如03:00-04:00 -20美元/小时，04:00-05:00 -19美元/小时等)

如何仅使用Excel (函数或VBA)完成此任务？

简单方法:输出到csv，并在python/php/等中进行处理。

Answer 1

这里有一个非VBA解决方案。这是个很恶心的公式，但很管用。我相信，用一些更有创意的方法，可以使它更容易使用和理解：

假设电子表格是这样设置的：

​

​

在单元格G1中输入此公式并向下拖放数据集：

=IF(ISBLANK(B2),"",IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2),SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B2,2),$Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2)),SUMIF($Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B2,2),$P$2:$P$24)))

为了详细解释这个公式：

1. 如果没有时间进行给定的人员/日组合，IF(ISBLANK(B2),""将返回一个空字符串。
2. LEFT(B2,2)将开始时间提取为一个小时。
3. Mid(B2,Find("-",B2)+1,2)将结束时间提取为一个小时.
4. IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2)将检查启动时间是否小于结束时间(意味着不需要加班)。如果启动时间小于结束时间，它将使用此公式计算每小时总成本：SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B3,2),$Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2))。
5. 如果启动时间高于结束时间(意为通宵工作)，它将使用此公式计算：SUMIF($Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B3,2),$P$2:$P$24)。
6. Find("-",[cell])的使用将开始时间和结束时间拆分为excel可以用来对时间/成本表进行计算的值。
7. 时间/成本表的Q列中的公式是=VALUE(MID(O2,FIND("-",O2)+1,2))，并将考虑成本的结束时间转换为Excel可以用来添加的值，而不是原始源格式的文本。

Answer 2

用VBA做这个！它是与生俱来的优秀和容易学习。在功能上，我会循环遍历表，编写一个函数，根据给定的信息计算挣来的美元。如果希望实时更新结果(如excel中的公式)，则可以编写用户定义函数。一个有用的函数可能是一个HoursIntersect函数，如下所示：

Public Function HoursIntersect(Period1Start As Date, Period1End As Date, _
                               Period2Start As Date, Period2End As Date) _
                               As Double
Dim result As Double

' Check if the ends are greater than the starts.  If they are, assume we are rolling over to
' a new day
If Period1End < Period1Start Then Period1End = Period1End + 1
If Period2End < Period2Start Then Period2End = Period2End + 1

With WorksheetFunction
result = .Min(Period1End, Period2End) - .Max(Period1Start, Period2Start)
HoursIntersect = .Max(result, 0) * 24
End With

End Function

然后，您可以通过拆分"-“字符上的值来确定开始时间和结束时间。然后将每个付款时间表乘以在该时间内工作的小时数：

DollarsEarned = DollarsEarned + 20 * HoursIntersect(StartTime, EndTime, #00:00:00#, #03:00:00#)
DollarsEarned = DollarsEarned + 10 * HoursIntersect(StartTime, EndTime, #09:00:00#, #21:00:00#)
DollarsEarned = DollarsEarned + 15 * HoursIntersect(StartTime, EndTime, #21:00:00#, #00:00:00#)

Answer 3

我有一种只使用公式的方法。首先，创建一个查找表，其中包含K& L列中的每一个小时和每个速率，如下所示：

K       L
08:00   15
09:00   10
10:00   10
11:00   10
12:00   10
13:00   10
14:00   10
15:00   10
16:00   10
17:00   10
18:00   10
19:00   10
20:00   10
21:00   15
22:00   15
23:00   15

请在数字前面输入单引号，以文本形式输入小时。

然后，如果您的时间在单元格B2中，则可以使用这个公式计算总计：=SUM(“L”&MATCH(左，B2，5)，K2:K40，0)&“：l”&MATCH(右，B2，5)，K2:K40，0))

公式所做的就是获取工作时间的左右文本，使用MATCH查找查找表中的位置，该查找表用于创建一个范围地址，然后通过间接函数传递到SUM。

如果您需要担心分钟，那么您所需要做的就是创建一个更大的查找表，它每天都能容纳每一分钟。如果您的工作日持续到午夜，您可能需要添加一些额外的逻辑。