Hello,
I know it's against rules to cross post but I haven't been able to find a solution to my problem anywhere else. So I'm posting it here again. Please don't delete it
I'm trying to achieve something seems really difficult to, I asked about on some other forums including Microsoft tech community but no one seems to be able help me.
Okay,
Here's what I'm trying to do:
I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance during those 9 hours of shifts. They are content moderators and their performance is assessed by how many cases they complete. We have 2 different channels. So they appear twice in the raw data. That's no problem because using Pivot Table, I can filter and do calculations accordingly.
Morning shift: 08:00 - 17:00
Mid Shift : 16:00 - 01:00
Night Shift : 23:30 - 08:30
I'm using Slicer to switch between shifts.
Our tool provides the raw data without distinguishing shifts. So for example, In order to extract all shifts of 23.08, I select the time 08:00 - 17:00 on our tool for morning shift. I then have to extract the data for mid shift which is 16:00 - 01:00 then for night shift 23.09 23:30 24.09 08:30. We mark the first set of data as Morning, then the second is mid shift. Now the problem is since morning shift is between 08:00 - 17:00 and mid 16:00 - 01:00 for overlap hour of 16:00 - 17:00 we get duplicate data and then we mark them again as mid shift which isn't correct. So they appear in both morning shift and mid shift. and since we get duplicate results for the overlap hour the calculation gets incorrect. I want to filter away that second set of data or make Excel ignore it somehow.
Someone on forums suggested me a formula. I left it exactly how it was suggested. By his logic, I need to filter away overlap hours with a true or false logic but It's not working as intended.
Am I clear enough? If not I can provide more info.
I'm attaching the excel file I'm working on for you to see what I'm doing.
docs.google.com
I know it's against rules to cross post but I haven't been able to find a solution to my problem anywhere else. So I'm posting it here again. Please don't delete it
I'm trying to achieve something seems really difficult to, I asked about on some other forums including Microsoft tech community but no one seems to be able help me.
Okay,
Here's what I'm trying to do:
I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance during those 9 hours of shifts. They are content moderators and their performance is assessed by how many cases they complete. We have 2 different channels. So they appear twice in the raw data. That's no problem because using Pivot Table, I can filter and do calculations accordingly.
Morning shift: 08:00 - 17:00
Mid Shift : 16:00 - 01:00
Night Shift : 23:30 - 08:30
I'm using Slicer to switch between shifts.
Our tool provides the raw data without distinguishing shifts. So for example, In order to extract all shifts of 23.08, I select the time 08:00 - 17:00 on our tool for morning shift. I then have to extract the data for mid shift which is 16:00 - 01:00 then for night shift 23.09 23:30 24.09 08:30. We mark the first set of data as Morning, then the second is mid shift. Now the problem is since morning shift is between 08:00 - 17:00 and mid 16:00 - 01:00 for overlap hour of 16:00 - 17:00 we get duplicate data and then we mark them again as mid shift which isn't correct. So they appear in both morning shift and mid shift. and since we get duplicate results for the overlap hour the calculation gets incorrect. I want to filter away that second set of data or make Excel ignore it somehow.
Someone on forums suggested me a formula. I left it exactly how it was suggested. By his logic, I need to filter away overlap hours with a true or false logic but It's not working as intended.
Am I clear enough? If not I can provide more info.
I'm attaching the excel file I'm working on for you to see what I'm doing.
Daily Performance Tracker - Template .xlsx
Mods Queue 1,Queue 2,Total Case,Total AHT Case,AHT,Case,AHT Employee 6,1288,14.55,48,64.58,1336,34.5605,Employee 6,Mid Employee 8,1237,14.35,0,0.00,1237,14.34843333,Employee 8,Mid Employee 22,1203,16.31,22,56.53,1225,26.362975,Employee 22,Mid Employee 25,1105,14.68,89,63.37,1194,34.15396,Employe...
