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
 
Sort by date Sort by votes
Hiya - thank you just seen that
It all works fine now thank you except when the agent is forced logged out 23:59 then its still showing as 1
ie - if that person was due to log out at 16:00 and system logged them out which identified by the log out time being 23:59 - then on these occasions display 0
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
If you don't want to count End Time 23:59 then use this formula in column O

O2 copied down
=IF(M2<>"",(M2<>TIME(23,59,0))*(M2>=SUMIFS(F:F,$A:$A,$H2,$B:$B,$I2,$C:$C,$J2,$D:$D,$K2)),"")

M.
 
Upvote 0
Man - I love you - thank you

Another thing matey - i think this may need VBA but not sure

Currently i have created a table for schedule, actual and the output to make it easier to work with

The output table looks like this

[TABLE="width: 1029"]
<tbody>[TR]
[TD]Group Name[/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]5[/TD]
[TD]0[/TD]
[TD]60.00%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
</tbody>[/TABLE]


Ive used Countifs and sumifs formula

This for scheduled start =IF([@[Group Name]]="","",COUNTIFS(Schedules[Group],[@[Group Name]],Schedules[Start],">0"))
This for actual logged start =SUMIFS(Activity[Logged Start Time],Activity[Group],[@[Group Name]]) 'This is looking at the formula you gave me to log the start time (Helper column)

Now i dont know how many groups there are going to be and how many dates

Can i have it so the format is like this instead listing the dates with the unique groups? So that each time its always getting the correct number of groups and listing all the dates associated with that group - I can then add 1 more criteria to sumif and countif pointing to the date also

So want the output like this

[TABLE="width: 682"]
<tbody>[TR]
[TD]Group Name[/TD]
[TD]Date[/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]01/01/2018[/TD]
[TD] 5[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]60.00%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]02/01/2018[/TD]
[TD] 6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]03/01/2018[/TD]
[TD] 6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]04/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]05/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]01/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]02/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]03/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]04/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]05/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
So want the output like this

[TABLE="class: cms_table, width: 682"]
<tbody>[TR]
[TD]Group Name[/TD]
[TD]Date[/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]01/01/2018[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]60.00%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]02/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]03/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]04/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]05/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]01/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]02/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]03/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]04/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
[TR]
[TD]Group 2[/TD]
[TD]05/01/2018[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]16.67%[/TD]
[TD]50.00%[/TD]
[/TR]
</tbody>[/TABLE]

Sorry - it got pasted incorrectly the previous time
 
Upvote 0
Siyanna said:
Man - I love you - thank you
Click to expand...

Thank you :)

About your question
This is a different matter. Have you tried a code?
I think you should try some code and if you are facing some problem then you can create a new thread.

M.
 
Upvote 0
Hiya - thank you once again

Ive tried to use and create this code that extracts the unique group and dates and then resize it - Im not sure this is the best methos (Hopefully you will be able to advise) and then resize the table but at the minute the resize part of the code aint working saying
"the range wont work because it doesnt align with the existing table"

Here is the code

Code: 
Sub CopyUniqueValues()


Dim ws As Worksheet
Dim tbl As ListObject


Set ws = Worksheets("Test")
Set tbl = ws.ListObjects("OutputData")


    ws.Range("Schedules[#All]").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
    ws.Range("OutputData[[#Headers],[Group]:[Date]]"), Unique:=True
    
tbl.Resize Range(ws.Range("AC1").End(xlDown), ws.Range("AC1").End(xlDown).Offset(, 8))


End Sub
 
Upvote 0
Siyanna

Sorry, but I do not have time to help you now.
Perhaps a new Thread is better for other members to try to help.

M.
 
Upvote 0
Thank you for all your help
i really do appreciate it

the advanced filter works fine except the resizing bit which ill try and figure out

I just wanted to know if this was the right way to do it as im not sure if this is a good and efficient way of doing it with large data

again you have been a diamond - really appreciate it
 
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
57
Bernie Galve
B
A
  • Solved
Count entry by time
Replies
3
Views
164
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
258
tezza
tezza
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
455
Sundance_Kid
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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