Home

Finding the number of people who worked within a time period

%3CLINGO-SUB%20id%3D%22lingo-sub-810081%22%20slang%3D%22en-US%22%3EFinding%20the%20number%20of%20people%20who%20worked%20within%20a%20time%20period%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810081%22%20slang%3D%22en-US%22%3E%3CP%3EBackstory%3A%20I%20work%20for%20a%20staffing%20company%20who%20is%20recruiting%2Fmanaging%20people%20who%20work%20on%20the%20phone%20at%20home.%20We%20need%20to%20report%20back%20to%20the%20client%20on%20how%20many%20people%20we%20have%20scheduled%20at%20every%20interval%20of%20time%20and%20how%20many%20people%20actually%20worked%20during%20those%20same%20intervals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20recently%20introduced%20a%20new%20online%20scheduling%20tool%20(Deputy)%2C%20which%20is%20a%20dream%2C%20but%20the%20report%20features%20are%20lacking.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20hired%20someone%20to%20use%20the%20API%20and%20export%20the%20live%20data%20into%20excel.%20Now%20I%20need%20to%20make%20something%20of%20it%20and%20I'm%20not%20exactly%20sure%20how%20to%20best%20go%20about%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20pictures%20of%20the%20raw%20data%20for%20just%20the%20schedule.%20Start%20Time%20and%20End%20Times%20of%20the%20shifts%20are%20in%20milliseconds%20of%20the%20day.%20I%20need%20to%20be%20able%20to%20filter%20it%20for%20the%20date%2C%20and%20the%20organizational%20unit%20(or%20department)%2C%20then%20count%20how%20many%20shifts%20were%20scheduled.%20Our%20intervals%20are%2015minutes%20long%2C%20so%20the%20first%20one%20of%20the%20day%20would%20be%20from%2000%3A00%20until%2000%3A15%2C%20then%2000%3A15-00%3A30%2C%20etc.%20I%20have%20also%20attached%20the%20desired%20end%20result%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20SUMPRODUCT%2C%20COUNTIF%2C%20ARRAYs.%20But%20I%20don't%20get%20what%20I%20need.%20While%20my%20excel%20work%20is%20strong%2C%20pivot%20tables%20confuse%20me%20and%20I%20don't%20know%20if%20that%20is%20the%20option%20here%3F%20I%20have%20access%20to%20SharePoint%2C%20Flow%20and%20Power%20BI%20if%20anyone%20thinks%20these%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20hel%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127402i3550DB0578460E6B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Roster%20Export.PNG%22%20title%3D%22Roster%20Export.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Eraw%20api%20data%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127403iE0B2894D7BCE9D2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22compare%20staffing.PNG%22%20title%3D%22compare%20staffing.PNG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Edesired%20future%20state%3C%2FSPAN%3E%3C%2FSPAN%3Ep%20me!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-810081%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
cryh548
Occasional Visitor

Backstory: I work for a staffing company who is recruiting/managing people who work on the phone at home. We need to report back to the client on how many people we have scheduled at every interval of time and how many people actually worked during those same intervals.

 

We recently introduced a new online scheduling tool (Deputy), which is a dream, but the report features are lacking. 

 

So I hired someone to use the API and export the live data into excel. Now I need to make something of it and I'm not exactly sure how to best go about it. 

 

I've attached pictures of the raw data for just the schedule. Start Time and End Times of the shifts are in milliseconds of the day. I need to be able to filter it for the date, and the organizational unit (or department), then count how many shifts were scheduled. Our intervals are 15minutes long, so the first one of the day would be from 00:00 until 00:15, then 00:15-00:30, etc. I have also attached the desired end result table. 

 

I have tried SUMPRODUCT, COUNTIF, ARRAYs. But I don't get what I need. While my excel work is strong, pivot tables confuse me and I don't know if that is the option here? I have access to SharePoint, Flow and Power BI if anyone thinks these could help.

 

Please helRoster Export.PNGraw api datacompare staffing.PNGdesired future statep me!

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies