-3
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

I want how much duration server A was used:

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

and how much time it was idle i.e

            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.

How can I do it in Excel or Google sheets

Steffen Winkler
  • 2,805
  • 2
  • 35
  • 58
Shivam Shrivastava
  • 33
  • 1
  • 7

2 Answers2

1

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:

enter image description here

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"))

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"))

The duration is given by

=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.

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Making some assumptions about your problem (your question could be better phrased) you could use an array formula. Cells in row E and cell H5 are formatted to shows hours

enter image description here enter image description here enter image description here

Michal Rosa
  • 2,459
  • 2
  • 15
  • 28