如何在Excel中的每个单元格中添加时间范围？(How to add time ranges in each cell in Excel?)

如何在Excel中的每个单元格中添加时间范围？(How to add time ranges in each cell in Excel?)

我有一张Excel工作表，管理员每周都会使用代理的工作时间手动更新。 我希望能够计算每天所有代理商的小时数。

Excel工作表看起来像这样：

+-----------+-------------+-------------+-------------+ | AME | MODAY | TUESDAY | WEDESDAY | +-----------+-------------+-------------+-------------+ | John | 8:0 - 17:00| 8:0 - 17:00| 8:0 - 17:00| +-----------+-------------+-------------+-------------+ | Peter | 8:00 - 16:0| 8:00 - 16:0| 8:00 - 16:0| +-----------+-------------+-------------+-------------+ | Craig | 9:00 - 17:0| 9:00 - 17:0| 9:00 - 17:0| +-----------+-------------+-------------+-------------+ | TOTAL | 8:0 | 8:0 | 8:0 | +-----------+-------------+-------------+-------------+

这有可能吗？ 如果可以的话，它会为很多人节省很多时间。

I have an Excel sheet that a manager updates manually with agent's work times, every week. I want to be able to calculate the number of hours for all agents for each day.

The Excel sheet looks something like this:

+-----------+-------------+-------------+-------------+ | AME | MODAY | TUESDAY | WEDESDAY | +-----------+-------------+-------------+-------------+ | John | 8:0 - 17:00| 8:0 - 17:00| 8:0 - 17:00| +-----------+-------------+-------------+-------------+ | Peter | 8:00 - 16:0| 8:00 - 16:0| 8:00 - 16:0| +-----------+-------------+-------------+-------------+ | Craig | 9:00 - 17:0| 9:00 - 17:0| 9:00 - 17:0| +-----------+-------------+-------------+-------------+ | TOTAL | 8:0 | 8:0 | 8:0 | +-----------+-------------+-------------+-------------+

Is this even possible? It would save a lot of time for a lot of people if it can be done.

最满意答案

使用LEFT和RIGHT的SUMPRODUCT（）来解析数据：

=SUMPRODUCT(RIGHT(TRIM(B2:B4),5)-LEFT(TRIM(B2:B4),5))

您需要在[hh]:mm的输出上使用自定义格式

use SUMPRODUCT() with LEFT and RIGHT to parse the data:

=SUMPRODUCT(RIGHT(TRIM(B2:B4),5)-LEFT(TRIM(B2:B4),5))

You will need to use a custom format on the output of [hh]:mm