连续日期中服务器的持续时间和空闲时间 [英] Duration and idle time for a server from continuous dates

问题描述

Server   Customer.              Start.                          End
A.               X.         12/10/2018 12:56    13/10/2018. 13:05
B.               K.         12/10/2018 14:05.   12/10/2018.  14:25
A.               N          12/10/2018.13:08.   13/10/2018. 17:09
A.               Y          15/10/2018.16:07.   17/10/2018. 14:09
A.               F.         18/10/2018 13:05.   18/10/2018 20:09

我想要使用服务器A的持续时间：

I want how much duration server A was used:

            12/10/2018 12:56 to 18/10/2018 20:09 

以及空闲时间，即

            13/10/2018 17:09 to 15/10/2018 16:07  
 duration + 17/10/2018 14:09 to 18/10/2018.

如果有任何客户登录，则该时间不算作空闲时间。

If any customer is login then that time is not counted as idle.

如何在Excel或Google表格中做到这一点

How can I do it in Excel or Google sheets

推荐答案

这是一个空白和孤岛问题。差距是用户数变为零的点与用户数大于零的点之间的间隔：

This is a 'gap and island' problem. A gap is the interval between the point where the number of users becomes zero and the point where the number of users becomes more than zero:

假设登录是按登录时间排序的，那么对于服务器A，我们需要从下一次登录时间中减去到目前为止的最新注销时间。给出以E2开头的公式：

Assuming the logins are sorted by login time, we need to subtract the latest logout time so far from the next login time, for server A, which gives this formula starting in E2:

=IF(A2<>$G$1,0,--TEXT(INDEX(C3:C$10,MATCH(TRUE,A3:A$10=$G$1,0))-MAX(IF(A$2:A2=$G$1,D$2:D2)),"general;\0"))

必须使用 Ctrl Shift作为数组公式输入 输入。对于Excel，可以使用Aggregate替代非数组公式：

Must be entered as an array formula using CtrlShiftEnter. An alternative non-array formula using Aggregate would be possible for Excel:

=IF(A2<>$G$1,0,--TEXT(INDEX(C3:C$10,AGGREGATE(15,6,ROW(A3:A$10)/(A3:A$10=$G$1),1)-ROW(A2))-AGGREGATE(14,6,D$2:D2/(A$2:A2=$G$1),1),"general;\0")) 

持续时间由

=MAX(IF(A2:A10=$G$1,D2:D10))-MIN(IF(A2:A10=$G$1,C2:C10))

空闲时间就是间隙的总和。

The idle time is just the sum of the gaps.

这篇关于连续日期中服务器的持续时间和空闲时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！