They they match formula

S

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Hi All

Hope you all are well

I have 2 sets of data where agents are scheduled to do an activity and another set of data of what agents actually did

I have listed a set of start and stop times for a paticular agent on that day

Agents are assigned to a group so there could be 10 agents in group 1 and 10 agents in group 2 etc

What im trying to check is to see if the 80% of the group have logged on the time they should have done and 80% of the group have logged off when they should have done but im struggling with a formula that gives me that information

The other problem i have is that agents some time could leave themselves logged on ie should have logged at 16:00 but system logged them out out at midnight. On those scenarios i want to highlight how many times in that group they left them selves logged in but in terms of whether the group had logged off - i want it to go against them as they should have logged off and this will bring the right behaviours

Can you please help me with a formula that gets me this out put

Here is an example of my data set

Schedule
Date ID start stop Time Group StartShift EndShift
09/04/2018 1117779 09:00 14:00 5 Group 1 09:00
09/04/2018 1117779 14:00 14:30 0.5 Group 1
09/04/2018 1117779 14:30 17:00 2.5 Group 1 17:00:00
10/04/2018 1117779 09:00 14:00 5 Group 1 09:00
10/04/2018 1117779 14:00 14:30 0.5 Group 1
10/04/2018 1117779 14:30 17:00 2.5 Group 1 17:00:00
11/04/2018 1117779 09:00 14:00 5 Group 1 09:00
11/04/2018 1117779 14:00 14:30 0.5 Group 1
11/04/2018 1117779 14:30 17:00 2.5 Group 1 17:00:00
12/04/2018 1117779 09:00 14:00 5 Group 1 09:00
12/04/2018 1117779 14:00 14:30 0.5 Group 1
12/04/2018 1117779 14:30 17:00 2.5 Group 1 17:00:00
13/04/2018 1117779 09:00 14:00 5 Group 1 09:00
13/04/2018 1117779 14:00 14:30 0.5 Group 1
13/04/2018 1117779 14:30 16:30 2 Group 1 16:30:00
16/04/2018 1117779 09:00 14:00 5 Group 2 09:00
16/04/2018 1117779 14:00 14:30 0.5 Group 2
16/04/2018 1117779 14:30 17:00 2.5 Group 2 17:00:00
17/04/2018 1117779 09:00 14:00 5 Group 2 09:00
17/04/2018 1117779 14:00 14:30 0.5 Group 2
17/04/2018 1117779 14:30 17:00 2.5 Group 2 17:00:00



Actual

Date ID start stop Time Group StartShift EndShift
04/05/2018 1117779 11:14 15:52 4.633333333 Group 2 11:14 15:52
09/05/2018 1117779 09:59 10:10 0.183333333 Group 1 09:59
09/05/2018 1117779 10:10 12:17 2.116666667 Group 1
09/05/2018 1117779 12:17 12:23 0.1 Group 1
09/05/2018 1117779 12:23 12:26 0.05 Group 1
09/05/2018 1117779 12:26 12:29 0.05 Group 1
09/05/2018 1117779 12:29 12:33 0.066666667 Group 1
09/05/2018 1117779 12:33 13:20 0.783333333 Group 1 13:20
09/05/2018 1117780 13:28 13:34 0.1 Group 2 13:28
09/05/2018 1117781 13:34 13:37 0.05 Group 2
09/05/2018 1117782 13:37 13:43 0.1 Group 2
09/05/2018 1117783 13:43 13:46 0.05 Group 2
09/05/2018 1117784 13:46 13:50 0.066666667 Group 2
09/05/2018 1117785 13:50 13:54 0.066666667 Group 2
09/05/2018 1117786 13:54 13:56 0.033333333 Group 2
09/05/2018 1117787 13:56 13:59 0.05 Group 2
09/05/2018 1117788 13:59 14:00 0.016666667 Group 2
09/05/2018 1117789 14:00 14:30 0.5 Group 2
09/05/2018 1117790 14:30 14:31 0.016666667 Group 2 14:31
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Another few things to add

If an agent logged in before their scheduled time then i want to class that as if they logged on time
if an agent logged out after scheduled time and not forced out by system - then i want to class that as they logged out on time
if they were logged out by system then that should go against them as they didnt log out on time

i want to then roll it all up to a group level to see if 80 % logged on and logged out correctly
 
Upvote 0
Apologies if i hav not been clear
please let me know if you need more information

thank you
 
Upvote 0
Hopefully this example will make more sense of what im after

Please not that the group are not sorted in the main source as thats how it needs to be

Thank You

Scheduled
Code: 
[TABLE="width: 395"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Group[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="align: right"]09:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="align: right"]15:30[/TD]
[/TR]
[TR]
[TD="align: right"]04-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]14:00[/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="align: right"]16:00[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]07:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]10:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]10:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="align: right"]14:30[/TD]
[/TR]
[TR]
[TD="align: right"]04-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]09:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
[TR]
[TD="align: right"]06-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="align: right"]15:00[/TD]
[/TR]
</tbody>[/TABLE]

Actual

Code: 
[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64"]Date[/TD]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Group[/TD]
[TD="width: 64"]Start[/TD]
[TD="width: 64"]End[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="class: xl66, align: right"]08:57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]15:03[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="class: xl66, align: right"]08:03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]15:59[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="class: xl66, align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]15:31[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]04-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="class: xl66, align: right"]08:00[/TD]
[TD="class: xl66, align: right"]14:02[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD="class: xl66, align: right"]08:02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 1[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]16:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="class: xl66, align: right"]08:57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]15:03[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="class: xl66, align: right"]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]15:31[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]04-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="class: xl66, align: right"]08:00[/TD]
[TD="class: xl66, align: right"]14:02[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="class: xl66, align: right"]09:02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]16:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]06-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD="class: xl66, align: right"]08:03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]06-Jan-18[/TD]
[TD="align: right"]1111[/TD]
[TD]Test 1[/TD]
[TD]Group 2[/TD]
[TD][/TD]
[TD="class: xl66, align: right"]23:59[/TD]
[/TR]
</tbody>[/TABLE]

Output Result

Code: 
[TABLE="width: 930"]
<tbody>[TR]
[TD][/TD]
[TD]Scheduled Start[/TD]
[TD]Actual Logged On Time[/TD]
[TD]Scheduled End[/TD]
[TD]Actual Logged End Time[/TD]
[TD]Forced Out by System[/TD]
[TD]% Logged On time[/TD]
[TD]% Logged Out On Time[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]60.00%[/TD]
[TD]80.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Maybe with two helper columns (gray area) - EDITED

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][td="bgcolor: #DCE6F1"]
L​
[/td][td="bgcolor: #DCE6F1"]
M​
[/td][td="bgcolor: #DCE6F1"]
N​
[/td][td="bgcolor: #DCE6F1"]
O​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Date​
[/td][td]
ID​
[/td][td]
Name​
[/td][td]
Group​
[/td][td]
Start​
[/td][td]
End​
[/td][td][/td][td]
Date​
[/td][td]
ID​
[/td][td]
Name​
[/td][td]
Group​
[/td][td]
Start​
[/td][td]
End​
[/td][td]
Logged On Time​
[/td][td]
Logged End Time​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
09:00​
[/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:57​
[/td][td][/td][td="bgcolor:#F2F2F2"]
1​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
15:00​
[/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
15:03​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td][/td][td][/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:03​
[/td][td][/td][td="bgcolor:#F2F2F2"]
0​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
16:00​
[/td][td][/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
15:59​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td][/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td][/td][td="bgcolor:#F2F2F2"]
1​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
15:30​
[/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
15:31​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
04/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td]
14:00​
[/td][td][/td][td]
04/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td]
14:02​
[/td][td="bgcolor:#F2F2F2"]
1​
[/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:00​
[/td][td][/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td]
08:02​
[/td][td][/td][td="bgcolor:#F2F2F2"]
0​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
16:00​
[/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 1​
[/td][td][/td][td]
16:00​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
07:00​
[/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:57​
[/td][td][/td][td="bgcolor:#F2F2F2"]
0​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23​
[/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:00​
[/td][td][/td][td]
01/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:03​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
24​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
10:00​
[/td][td][/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:00​
[/td][td][/td][td="bgcolor:#F2F2F2"]
1​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
25​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
26​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:31​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
27​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
04/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:00​
[/td][td]
14:02​
[/td][td="bgcolor:#F2F2F2"]
1​
[/td][td="bgcolor:#F2F2F2"]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
28​
[/td][td]
02/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:00​
[/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
09:02​
[/td][td][/td][td="bgcolor:#F2F2F2"]
0​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
29​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
10:00​
[/td][td][/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
30​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
16:00​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
31​
[/td][td]
03/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
14:30​
[/td][td][/td][td]
06/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:03​
[/td][td][/td][td="bgcolor:#F2F2F2"]
0​
[/td][td="bgcolor:#F2F2F2"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
32​
[/td][td]
04/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:00​
[/td][td]
15:00​
[/td][td][/td][td]
06/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
23:59​
[/td][td="bgcolor:#F2F2F2"][/td][td="bgcolor:#F2F2F2"]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
33​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
09:00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
34​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
35​
[/td][td]
05/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
36​
[/td][td]
06/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td]
08:00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
37​
[/td][td]
06/jan/18​
[/td][td]
1111​
[/td][td]
Test 1​
[/td][td]
Group 2​
[/td][td][/td][td]
15:00​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
38​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


Formula in N2 copied to O2 and down
=IF(L2<>"",--(L2<=SUMIFS(E:E,$A:$A,$H2,$B:$B,$I2,$C:$C,$J2,$D:$D,$K2)),"")

1 means Logged On Time; 0 means Logged Off Time

Then you can use COUNTIFS in columns N and O to get the count for each group

M.
 
Last edited:
Upvote 0
This is correct


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[TD="bgcolor: #DCE6F1"]
L​
[/TD]
[TD="bgcolor: #DCE6F1"]
M​
[/TD]
[TD="bgcolor: #DCE6F1"]
N​
[/TD]
[TD="bgcolor: #DCE6F1"]
O​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Date​
[/TD]
[TD]
ID​
[/TD]
[TD]
Name​
[/TD]
[TD]
Group​
[/TD]
[TD]
Start​
[/TD]
[TD]
End​
[/TD]
[TD][/TD]
[TD]
Date​
[/TD]
[TD]
ID​
[/TD]
[TD]
Name​
[/TD]
[TD]
Group​
[/TD]
[TD]
Start​
[/TD]
[TD]
End​
[/TD]
[TD]
Logged Start Time
​
[/TD]
[TD]
Logged End Time​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
09:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:57​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
15:03​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:03​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
16:00​
[/TD]
[TD][/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
15:59​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
15:30​
[/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
15:31​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
04/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD]
14:00​
[/TD]
[TD][/TD]
[TD]
04/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD]
14:02​
[/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD]
08:02​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
16:00​
[/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 1​
[/TD]
[TD][/TD]
[TD]
16:00​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
07:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:57​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD]
01/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:03​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
10:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
26​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:31​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
27​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
04/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:00​
[/TD]
[TD]
14:02​
[/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[TD="bgcolor: #F2F2F2"]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
28​
[/TD]
[TD]
02/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
09:02​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
29​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
10:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
16:00​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
31​
[/TD]
[TD]
03/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
14:30​
[/TD]
[TD][/TD]
[TD]
06/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:03​
[/TD]
[TD][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
32​
[/TD]
[TD]
04/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:00​
[/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD]
06/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
23:59​
[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
33​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
09:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
34​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
35​
[/TD]
[TD]
05/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
36​
[/TD]
[TD]
06/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
37​
[/TD]
[TD]
06/jan/18​
[/TD]
[TD]
1111​
[/TD]
[TD]
Test 1​
[/TD]
[TD]
Group 2​
[/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
38​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


See formulas above

M.
 
Last edited:
Upvote 0
oops...

The formula in column O should be
O2 copied down
=IF(M2<>"",--(M2>=SUMIFS(F:F,$A:$A,$H2,$B:$B,$I2,$C:$C,$J2,$D:$D,$K2)),"")

M.
 
Upvote 0
Thank you so much for responding to me - i think there results aint 100% right

for group 1 date 01/01/18
the sched end time is 15:00
the agent logged out after 15:03 so that should say that he logged out ok because he didnt log out before his scheduled time. If the agent was forced logged out at 23:59 then that should say 0 and treat it as if they logged out before their scheduled time as we dont know whether they did or did not log out on time

Hope this makes sense

Again thank You
 
Upvote 0
Siyanna said:
Thank you so much for responding to me - i think there results aint 100% right

for group 1 date 01/01/18
the sched end time is 15:00
the agent logged out after 15:03 so that should say that he logged out ok because he didnt log out before his scheduled time. If the agent was forced logged out at 23:59 then that should say 0 and treat it as if they logged out before their scheduled time as we dont know whether they did or did not log out on time

Hope this makes sense

Again thank You
Click to expand...

Have you seen the formula in post 8 (new version for column O)?

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top