我需要一个公式来计算在各种任务上花费的总时间，但我希望计算出每天在相关时间间隔内的总分配时间。
例如，A 列表示开始时间，B 列表示结束时间。
C 列和 D 列的间隔为 30 分钟，范围从 07:00 到 23:00。
在 E 列中，我需要一个公式来计算从 09:07-10:56 完成一项任务所花费的时间，但要填充相关的 30 分钟间隔，即 09:00-09:30 将是 23 分钟，09:30-10:00 将是 30 分钟等等。
我有数千个任务，因此当我添加新任务时，我希望得到累计总数。
答案1
我真的希望我可以附加文件
如果有人有解决方案，请告诉我，
我有解决方案给你。首先，一些设置：
您可以使用动态命名范围设置相同的内容，并在公式中使用它们，但我喜欢表格，所以我使用了表格。
我在 A:B 列中创建了一个名为 tblTimeLog 的表格，
它有两个字段：开始、停止
，您可以根据需要在此处输入所有开始和停止工作的时间
我在 D:G 列中创建了第二个表，名为 tblTimeSummary，
它有 4 个字段：间隔开始、间隔停止、间隔、总时间
间隔开始和停止方便
您可以使用显示时间跨度的单个文本字段完成所有操作，但如果将其分开，公式会简单得多。
这两个将保存时间范围，如 07:00 和 07:30、07:30 和 08:00 等。
我手动输入这些内容，自动填充
间隔只是为了演示而将两者结合起来，这样你就会得到诸如“07:00 - 07:30”、“07:30 - 08:00”等内容。
我在这里使用了一个公式：=TEXT([@[Interval Start]],"HH:mm") & " - " & TEXT([@[Interval Stop]],"HH:mm")
总时间将计算出在该间隔内完成了多少工作。
以下是总时间的主要公式：
=SUMPRODUCT((tblTimeLog[Start]<=[@[Interval Stop]])*(tblTimeLog[Stop]>[@[Interval Start]])*(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])-IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])))*24*60
让我们分解一下
SUMPRODUCT，创建数组，将每行相乘，然后对这些行求和。
重要的是要知道这将被视为TRUE1 和FALSE0
第一个数组是(tblTimeLog[Start]<=[@[Interval Stop]])
查找在此间隔结束之前开始的所有日志条目
第二个数组的想法相反，(tblTimeLog[Stop]>[@[Interval Start]])
它查找在此间隔开始后结束的所有日志条目
，它们一起查找在间隔内有一定时间的所有日志条目
(IF([@[Interval Stop]]<=tblTimeLog[Stop],[@[Interval Stop]],tblTimeLog[Stop])
此数组选择间隔的结束或活动的结束，以两者中任何一个为准第一的
IF([@[Interval Start]]>tblTimeLog[Start],[@[Interval Start]],tblTimeLog[Start])
此数组选择间隔的开始或活动的开始，以两者中任何一个为准最后的
最后两个数组之间的差异告诉你在这个特定间隔内每个日志条目花费了多少时间。
*24*60最后的这一部分只是将天数的时间值转换为分钟数的值
让我们用一些伪代码来总结一下：
=Sum((If the log entry is in this interval)*(How much time was spent in this interval))
第一个数组将是 1 或 0，第二个数组将是一些时间值。
将它们相乘得到一个由 0 和时间值组成的数组
。相加后得到该间隔内记录的总时间