Hello everyone,
I have an excel sheet I have created. I am using Microsoft excel 2013.
I have two sheets on this excel
Data and Pivot are the two sheet names.
On the data sheet I have 12 columns and from these I am making a pivot table on the "Pivot" sheet. Next to that table I have created several cells with formulas that find the lowest start time and the highest start time.
The start times are tied to orders that are being picked within a warehouse.
Here is an example from column A to column L on the DATA Page. You can see there are two pickers picking the same warehouse order.
[TABLE="width: 1304"]
<tbody>[TR]
[TD]Warehouse Order[/TD]
[TD]Warehouse Task[/TD]
[TD]Activity Area[/TD]
[TD]Start Date[/TD]
[TD]Start Time[/TD]
[TD]Warehouse Task Status[/TD]
[TD]Dest. Handling Unit[/TD]
[TD]Act.Qty Dest. Alt.Un[/TD]
[TD]Alt. Unit of Measure[/TD]
[TD]Confirmed by[/TD]
[TD]Confirmation Date[/TD]
[TD]Confirmation Time[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535432[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]13:35:50[/TD]
[TD]C[/TD]
[TD]343357545000211481[/TD]
[TD]2[/TD]
[TD]K01[/TD]
[TD]30001283[/TD]
[TD]26/09/2016[/TD]
[TD]13:39:47[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535478[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]13:39:48[/TD]
[TD]C[/TD]
[TD]343357545000211481[/TD]
[TD]1[/TD]
[TD]K01[/TD]
[TD]30001283[/TD]
[TD]26/09/2016[/TD]
[TD]13:41:44[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1304"]
<tbody>[TR]
[TD]5000090338[/TD]
[TD]3000535455[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]17:21:19[/TD]
[TD]C[/TD]
[TD]343357545000212563[/TD]
[TD]3[/TD]
[TD]K01[/TD]
[TD]30001662[/TD]
[TD]26/09/2016[/TD]
[TD]17:22:50[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535457[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]17:22:51[/TD]
[TD]C[/TD]
[TD]343357545000212563[/TD]
[TD]2[/TD]
[TD]K01[/TD]
[TD]30001662[/TD]
[TD]26/09/2016[/TD]
[TD]17:23:41[/TD]
[/TR]
</tbody>[/TABLE]
The next sheet has several formulas that are referencing both the pivot table cells on the "pivot" sheet and the "data" sheet. Here is an example of the "pivot" sheet.
[TABLE="width: 1283"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg rate Overall[/TD]
[TD][/TD]
[TD]Picker #[/TD]
[TD][/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]Start of 1st Task[/TD]
[TD][/TD]
[TD]Last Task Confirmed[/TD]
[TD][/TD]
[TD]Total Time Per WO[/TD]
[TD][/TD]
[TD]Total Total time cum. All Orders[/TD]
[TD][/TD]
[TD]Total CPH Per WO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Act.Qty Dest. Alt.Un[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30000120[/TD]
[TD="align: right"]818[/TD]
[TD][/TD]
[TD="align: right"]174.7241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:04:14[/TD]
[TD][/TD]
[TD]13:45:08[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]00:00:00[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]5000090364[/TD]
[TD="align: right"]818[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000120[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:04:14[/TD]
[TD][/TD]
[TD]13:45:08[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]30000132[/TD]
[TD="align: right"]185[/TD]
[TD][/TD]
[TD="align: right"]39.3524[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17:29:15[/TD]
[TD][/TD]
[TD]19:23:03[/TD]
[TD][/TD]
[TD]01:53:48[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]5000090353[/TD]
[TD="align: right"]185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000132[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]17:02:01[/TD]
[TD][/TD]
[TD]19:23:03[/TD]
[TD][/TD]
[TD]02:21:02[/TD]
[TD][/TD]
[TD]04:42:04[/TD]
[TD][/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]30000311[/TD]
[TD="align: right"]578[/TD]
[TD][/TD]
[TD="align: right"]122.9279[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:19:00[/TD]
[TD][/TD]
[TD]14:01:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]5000090347[/TD]
[TD="align: right"]578[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000311[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:19:00[/TD]
[TD][/TD]
[TD]14:01:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]30000346[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000090340[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001456[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:51:29[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]07:57:07[/TD]
[TD][/TD]
[TD]15:54:14[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30000689[/TD]
[TD="align: right"]373[/TD]
[TD][/TD]
[TD="align: right"]24.56012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:05:32[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]01:50:35[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]202[/TD]
[/TR]
[TR]
[TD]5000090156[/TD]
[TD="align: right"]373[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000689[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]14:20:30[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]07:35:37[/TD]
[TD][/TD]
[TD]15:11:14[/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]30000886[/TD]
[TD="align: right"]264[/TD]
[TD][/TD]
[TD="align: right"]175.1567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:21:40[/TD]
[TD][/TD]
[TD]09:54:37[/TD]
[TD][/TD]
[TD]01:32:57[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]5000090312[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000886[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:21:39[/TD]
[TD][/TD]
[TD]08:24:22[/TD]
[TD][/TD]
[TD]00:02:43[/TD]
[TD][/TD]
[TD]01:30:26[/TD]
[TD][/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]5000090328[/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000886[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:26:54[/TD]
[TD][/TD]
[TD]09:54:37[/TD]
[TD][/TD]
[TD]01:27:43[/TD]
[TD][/TD]
[TD]01:30:26[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]30001158[/TD]
[TD="align: right"]1030.5[/TD]
[TD][/TD]
[TD="align: right"]196.5041[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:23:33[/TD]
[TD][/TD]
[TD]15:05:25[/TD]
[TD][/TD]
[TD]06:41:52[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]154[/TD]
[/TR]
[TR]
[TD]5000090311[/TD]
[TD="align: right"]614.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001158[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:23:33[/TD]
[TD][/TD]
[TD]11:57:16[/TD]
[TD][/TD]
[TD]03:33:43[/TD]
[TD][/TD]
[TD]05:14:39[/TD]
[TD][/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]5000090350[/TD]
[TD="align: right"]416[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001158[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:24:29[/TD]
[TD][/TD]
[TD]15:05:25[/TD]
[TD][/TD]
[TD]01:40:56[/TD]
[TD][/TD]
[TD]05:14:39[/TD]
[TD][/TD]
[TD]247[/TD]
[/TR]
[TR]
[TD]30001251[/TD]
[TD="align: right"]714[/TD]
[TD][/TD]
[TD="align: right"]129.6872[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:56:28[/TD]
[TD][/TD]
[TD]14:21:19[/TD]
[TD][/TD]
[TD]05:24:51[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]5000090335[/TD]
[TD="align: right"]714[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001251[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:50:59[/TD]
[TD][/TD]
[TD]14:21:19[/TD]
[TD][/TD]
[TD]05:30:20[/TD]
[TD][/TD]
[TD]05:30:20[/TD]
[TD][/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]30001283[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD="align: right"]14.91154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13:39:47[/TD]
[TD][/TD]
[TD]14:39:37[/TD]
[TD][/TD]
[TD]00:59:50[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]5000090338
[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]30001283[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:35:50[/TD]
[TD][/TD]
[TD]19:31:56[/TD]
[TD][/TD]
[TD]05:56:06[/TD]
[TD][/TD]
[TD]11:52:12[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]30001333[/TD]
[TD="align: right"]393[/TD]
[TD][/TD]
[TD="align: right"]138.9102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:05:25[/TD]
[TD][/TD]
[TD]11:53:01[/TD]
[TD][/TD]
[TD]02:47:36[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]5000090368[/TD]
[TD="align: right"]393[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001333[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:03:16[/TD]
[TD][/TD]
[TD]11:53:01[/TD]
[TD][/TD]
[TD]02:49:45[/TD]
[TD][/TD]
[TD]02:49:45[/TD]
[TD][/TD]
[TD]139[/TD]
[/TR]
[TR]
[TD]30001407[/TD]
[TD="align: right"]249[/TD]
[TD][/TD]
[TD="align: right"]147.4827[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:56:43[/TD]
[TD][/TD]
[TD]22:35:40[/TD]
[TD][/TD]
[TD]01:38:57[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]151[/TD]
[/TR]
[TR]
[TD]5000091821[/TD]
[TD="align: right"]249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001407[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:54:22[/TD]
[TD][/TD]
[TD]22:35:40[/TD]
[TD][/TD]
[TD]01:41:18[/TD]
[TD][/TD]
[TD]01:41:18[/TD]
[TD][/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]30001456[/TD]
[TD="align: right"]779[/TD]
[TD][/TD]
[TD="align: right"]48.98173[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:55:10[/TD]
[TD][/TD]
[TD]14:20:44[/TD]
[TD][/TD]
[TD]05:25:34[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]144[/TD]
[/TR]
[TR]
[TD]5000090340[/TD]
[TD="align: right"]779[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001456[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:51:29[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]07:57:07[/TD]
[TD][/TD]
[TD]15:54:14[/TD]
[TD][/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]30001457[/TD]
[TD="align: right"]107[/TD]
[TD][/TD]
[TD="align: right"]107.3878[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21:39:31[/TD]
[TD][/TD]
[TD]22:39:18[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]5000091828[/TD]
[TD="align: right"]107[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001457[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]21:39:31[/TD]
[TD][/TD]
[TD]22:39:18[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]30001626[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD="align: right"]180.695[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:07:06[/TD]
[TD][/TD]
[TD]10:38:11[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]5000090183[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001626[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:07:06[/TD]
[TD][/TD]
[TD]10:38:11[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]30001634[/TD]
[TD="align: right"]138[/TD]
[TD][/TD]
[TD="align: right"]93.17329[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:41:58[/TD]
[TD][/TD]
[TD]22:18:01[/TD]
[TD][/TD]
[TD]01:36:03[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]5000090158[/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001634[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:41:58[/TD]
[TD][/TD]
[TD]21:50:22[/TD]
[TD][/TD]
[TD]01:08:24[/TD]
[TD][/TD]
[TD]01:28:52[/TD]
[TD][/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]5000093083[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001634[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]21:57:33[/TD]
[TD][/TD]
[TD]22:18:01[/TD]
[TD][/TD]
[TD]00:20:28[/TD]
[TD][/TD]
[TD]01:28:52[/TD]
[TD][/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]30001640[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14:29:14[/TD]
[TD][/TD]
[TD]14:29:14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000090156[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000689[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]14:20:30[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]07:35:37[/TD]
[TD][/TD]
[TD]15:11:14[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30001650[/TD]
[TD="align: right"]529[/TD]
[TD][/TD]
[TD="align: right"]195.5638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:59:46[/TD]
[TD][/TD]
[TD]22:38:03[/TD]
[TD][/TD]
[TD]09:38:17[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]5000092065[/TD]
[TD="align: right"]371[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001650[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:59:05[/TD]
[TD][/TD]
[TD]22:38:03[/TD]
[TD][/TD]
[TD]01:38:58[/TD]
[TD][/TD]
[TD]02:42:18[/TD]
[TD][/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]5000091825[/TD]
[TD="align: right"]158[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001650[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]12:59:46[/TD]
[TD][/TD]
[TD]14:03:06[/TD]
[TD][/TD]
[TD]01:03:20[/TD]
[TD][/TD]
[TD]02:42:18[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]30001662[/TD]
[TD="align: right"]757[/TD]
[TD][/TD]
[TD="align: right"]194.9496[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17:06:24[/TD]
[TD][/TD]
[TD]23:25:39[/TD]
[TD][/TD]
[TD]06:19:15[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD="align: right"]302[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]30001283[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:35:50[/TD]
[TD][/TD]
[TD]19:31:56[/TD]
[TD][/TD]
[TD]05:56:06[/TD]
[TD][/TD]
[TD]11:52:12[/TD]
[TD][/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]5000093079[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001662[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]19:32:40[/TD]
[TD][/TD]
[TD]23:25:39[/TD]
[TD][/TD]
[TD]03:52:59[/TD]
[TD][/TD]
[TD]03:52:59[/TD]
[TD][/TD]
[TD]117[/TD]
[/TR]
[TR]
[TD]30001668[/TD]
[TD="align: right"]172[/TD]
[TD][/TD]
[TD="align: right"]150.0363[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21:37:46[/TD]
[TD][/TD]
[TD]22:43:56[/TD]
[TD][/TD]
[TD]01:06:10[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]156[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 3129"]
<tbody>[TR]
[TD]5000090338[/TD]
[TD]177[/TD]
[TD][/TD]
[TD]=IFERROR(B23/D23,"")[/TD]
[TD][/TD]
[TD]=IFERROR(VLOOKUP(A23,Data!$1:$1048576,10,0),"")[/TD]
[TD][/TD]
[TD]=IFERROR(VLOOKUP(A23,Data!$1:$1048576,3,0),"")[/TD]
[TD][/TD]
[TD]=IF(A23<5000000000,"",(MIN(IF(Data!A:A=A23,Data!E:E,IF(Data!J:J=A23,Data!L:L,1)))))[/TD]
[TD][/TD]
[TD]=IFERROR(MAX(IF(Data!A:A=A23,Data!L:L,IF(A23<5000000000,"",IF(Data!J:J=A23,Data!L:L,0)))),"")[/TD]
[TD][/TD]
[TD]=IFERROR(IF(M23-K23>0,M23-K23,""),"")[/TD]
[TD][/TD]
[TD]=SUMIF(G:G,G23,O:O)[/TD]
[TD][/TD]
[TD]=IFERROR(IF(O23>0.20833,B23/((O23-$U$3)*24),B23/(O23*24)),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
These are the pivot table numbers, followed the formulas used in the "Pivot" sheet.
I have put the cells in red that are the main issue here. But if it happens at all it will not give an accurate report. Does anyone know how to find the end time that a picker finished and then the new start time.
Please let me know if you can help.
maybe someone even knows a better way to use a macro to get this information. The formulas really take a while. I dont know how to attach the file on here.
I have an excel sheet I have created. I am using Microsoft excel 2013.
I have two sheets on this excel
Data and Pivot are the two sheet names.
On the data sheet I have 12 columns and from these I am making a pivot table on the "Pivot" sheet. Next to that table I have created several cells with formulas that find the lowest start time and the highest start time.
The start times are tied to orders that are being picked within a warehouse.
Here is an example from column A to column L on the DATA Page. You can see there are two pickers picking the same warehouse order.
[TABLE="width: 1304"]
<tbody>[TR]
[TD]Warehouse Order[/TD]
[TD]Warehouse Task[/TD]
[TD]Activity Area[/TD]
[TD]Start Date[/TD]
[TD]Start Time[/TD]
[TD]Warehouse Task Status[/TD]
[TD]Dest. Handling Unit[/TD]
[TD]Act.Qty Dest. Alt.Un[/TD]
[TD]Alt. Unit of Measure[/TD]
[TD]Confirmed by[/TD]
[TD]Confirmation Date[/TD]
[TD]Confirmation Time[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535432[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]13:35:50[/TD]
[TD]C[/TD]
[TD]343357545000211481[/TD]
[TD]2[/TD]
[TD]K01[/TD]
[TD]30001283[/TD]
[TD]26/09/2016[/TD]
[TD]13:39:47[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535478[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]13:39:48[/TD]
[TD]C[/TD]
[TD]343357545000211481[/TD]
[TD]1[/TD]
[TD]K01[/TD]
[TD]30001283[/TD]
[TD]26/09/2016[/TD]
[TD]13:41:44[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1304"]
<tbody>[TR]
[TD]5000090338[/TD]
[TD]3000535455[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]17:21:19[/TD]
[TD]C[/TD]
[TD]343357545000212563[/TD]
[TD]3[/TD]
[TD]K01[/TD]
[TD]30001662[/TD]
[TD]26/09/2016[/TD]
[TD]17:22:50[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD]3000535457[/TD]
[TD]A1S0[/TD]
[TD]26/09/2016[/TD]
[TD]17:22:51[/TD]
[TD]C[/TD]
[TD]343357545000212563[/TD]
[TD]2[/TD]
[TD]K01[/TD]
[TD]30001662[/TD]
[TD]26/09/2016[/TD]
[TD]17:23:41[/TD]
[/TR]
</tbody>[/TABLE]
The next sheet has several formulas that are referencing both the pivot table cells on the "pivot" sheet and the "data" sheet. Here is an example of the "pivot" sheet.
[TABLE="width: 1283"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Avg rate Overall[/TD]
[TD][/TD]
[TD]Picker #[/TD]
[TD][/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]Start of 1st Task[/TD]
[TD][/TD]
[TD]Last Task Confirmed[/TD]
[TD][/TD]
[TD]Total Time Per WO[/TD]
[TD][/TD]
[TD]Total Total time cum. All Orders[/TD]
[TD][/TD]
[TD]Total CPH Per WO[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Sum of Act.Qty Dest. Alt.Un[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30000120[/TD]
[TD="align: right"]818[/TD]
[TD][/TD]
[TD="align: right"]174.7241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:04:14[/TD]
[TD][/TD]
[TD]13:45:08[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]00:00:00[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]5000090364[/TD]
[TD="align: right"]818[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000120[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:04:14[/TD]
[TD][/TD]
[TD]13:45:08[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]04:40:54[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]30000132[/TD]
[TD="align: right"]185[/TD]
[TD][/TD]
[TD="align: right"]39.3524[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17:29:15[/TD]
[TD][/TD]
[TD]19:23:03[/TD]
[TD][/TD]
[TD]01:53:48[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]5000090353[/TD]
[TD="align: right"]185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000132[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]17:02:01[/TD]
[TD][/TD]
[TD]19:23:03[/TD]
[TD][/TD]
[TD]02:21:02[/TD]
[TD][/TD]
[TD]04:42:04[/TD]
[TD][/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]30000311[/TD]
[TD="align: right"]578[/TD]
[TD][/TD]
[TD="align: right"]122.9279[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:19:00[/TD]
[TD][/TD]
[TD]14:01:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]5000090347[/TD]
[TD="align: right"]578[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000311[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:19:00[/TD]
[TD][/TD]
[TD]14:01:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]04:42:07[/TD]
[TD][/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]30000346[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000090340[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001456[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:51:29[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]07:57:07[/TD]
[TD][/TD]
[TD]15:54:14[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30000689[/TD]
[TD="align: right"]373[/TD]
[TD][/TD]
[TD="align: right"]24.56012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:05:32[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]01:50:35[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]202[/TD]
[/TR]
[TR]
[TD]5000090156[/TD]
[TD="align: right"]373[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000689[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]14:20:30[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]07:35:37[/TD]
[TD][/TD]
[TD]15:11:14[/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]30000886[/TD]
[TD="align: right"]264[/TD]
[TD][/TD]
[TD="align: right"]175.1567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:21:40[/TD]
[TD][/TD]
[TD]09:54:37[/TD]
[TD][/TD]
[TD]01:32:57[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]5000090312[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000886[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:21:39[/TD]
[TD][/TD]
[TD]08:24:22[/TD]
[TD][/TD]
[TD]00:02:43[/TD]
[TD][/TD]
[TD]01:30:26[/TD]
[TD][/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]5000090328[/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000886[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:26:54[/TD]
[TD][/TD]
[TD]09:54:37[/TD]
[TD][/TD]
[TD]01:27:43[/TD]
[TD][/TD]
[TD]01:30:26[/TD]
[TD][/TD]
[TD]175[/TD]
[/TR]
[TR]
[TD]30001158[/TD]
[TD="align: right"]1030.5[/TD]
[TD][/TD]
[TD="align: right"]196.5041[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:23:33[/TD]
[TD][/TD]
[TD]15:05:25[/TD]
[TD][/TD]
[TD]06:41:52[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]154[/TD]
[/TR]
[TR]
[TD]5000090311[/TD]
[TD="align: right"]614.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001158[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:23:33[/TD]
[TD][/TD]
[TD]11:57:16[/TD]
[TD][/TD]
[TD]03:33:43[/TD]
[TD][/TD]
[TD]05:14:39[/TD]
[TD][/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]5000090350[/TD]
[TD="align: right"]416[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001158[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:24:29[/TD]
[TD][/TD]
[TD]15:05:25[/TD]
[TD][/TD]
[TD]01:40:56[/TD]
[TD][/TD]
[TD]05:14:39[/TD]
[TD][/TD]
[TD]247[/TD]
[/TR]
[TR]
[TD]30001251[/TD]
[TD="align: right"]714[/TD]
[TD][/TD]
[TD="align: right"]129.6872[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:56:28[/TD]
[TD][/TD]
[TD]14:21:19[/TD]
[TD][/TD]
[TD]05:24:51[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]5000090335[/TD]
[TD="align: right"]714[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001251[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:50:59[/TD]
[TD][/TD]
[TD]14:21:19[/TD]
[TD][/TD]
[TD]05:30:20[/TD]
[TD][/TD]
[TD]05:30:20[/TD]
[TD][/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]30001283[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD="align: right"]14.91154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13:39:47[/TD]
[TD][/TD]
[TD]14:39:37[/TD]
[TD][/TD]
[TD]00:59:50[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]177[/TD]
[/TR]
[TR]
[TD]5000090338
[/TD]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]30001283[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:35:50[/TD]
[TD][/TD]
[TD]19:31:56[/TD]
[TD][/TD]
[TD]05:56:06[/TD]
[TD][/TD]
[TD]11:52:12[/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]30001333[/TD]
[TD="align: right"]393[/TD]
[TD][/TD]
[TD="align: right"]138.9102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09:05:25[/TD]
[TD][/TD]
[TD]11:53:01[/TD]
[TD][/TD]
[TD]02:47:36[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]5000090368[/TD]
[TD="align: right"]393[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001333[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]09:03:16[/TD]
[TD][/TD]
[TD]11:53:01[/TD]
[TD][/TD]
[TD]02:49:45[/TD]
[TD][/TD]
[TD]02:49:45[/TD]
[TD][/TD]
[TD]139[/TD]
[/TR]
[TR]
[TD]30001407[/TD]
[TD="align: right"]249[/TD]
[TD][/TD]
[TD="align: right"]147.4827[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:56:43[/TD]
[TD][/TD]
[TD]22:35:40[/TD]
[TD][/TD]
[TD]01:38:57[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]151[/TD]
[/TR]
[TR]
[TD]5000091821[/TD]
[TD="align: right"]249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001407[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:54:22[/TD]
[TD][/TD]
[TD]22:35:40[/TD]
[TD][/TD]
[TD]01:41:18[/TD]
[TD][/TD]
[TD]01:41:18[/TD]
[TD][/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]30001456[/TD]
[TD="align: right"]779[/TD]
[TD][/TD]
[TD="align: right"]48.98173[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:55:10[/TD]
[TD][/TD]
[TD]14:20:44[/TD]
[TD][/TD]
[TD]05:25:34[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]144[/TD]
[/TR]
[TR]
[TD]5000090340[/TD]
[TD="align: right"]779[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001456[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:51:29[/TD]
[TD][/TD]
[TD]16:48:36[/TD]
[TD][/TD]
[TD]07:57:07[/TD]
[TD][/TD]
[TD]15:54:14[/TD]
[TD][/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]30001457[/TD]
[TD="align: right"]107[/TD]
[TD][/TD]
[TD="align: right"]107.3878[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21:39:31[/TD]
[TD][/TD]
[TD]22:39:18[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]5000091828[/TD]
[TD="align: right"]107[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001457[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]21:39:31[/TD]
[TD][/TD]
[TD]22:39:18[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]00:59:47[/TD]
[TD][/TD]
[TD]107[/TD]
[/TR]
[TR]
[TD]30001626[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD="align: right"]180.695[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08:07:06[/TD]
[TD][/TD]
[TD]10:38:11[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]5000090183[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001626[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]08:07:06[/TD]
[TD][/TD]
[TD]10:38:11[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]02:31:05[/TD]
[TD][/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]30001634[/TD]
[TD="align: right"]138[/TD]
[TD][/TD]
[TD="align: right"]93.17329[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20:41:58[/TD]
[TD][/TD]
[TD]22:18:01[/TD]
[TD][/TD]
[TD]01:36:03[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]5000090158[/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001634[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:41:58[/TD]
[TD][/TD]
[TD]21:50:22[/TD]
[TD][/TD]
[TD]01:08:24[/TD]
[TD][/TD]
[TD]01:28:52[/TD]
[TD][/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]5000093083[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001634[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]21:57:33[/TD]
[TD][/TD]
[TD]22:18:01[/TD]
[TD][/TD]
[TD]00:20:28[/TD]
[TD][/TD]
[TD]01:28:52[/TD]
[TD][/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]30001640[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14:29:14[/TD]
[TD][/TD]
[TD]14:29:14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000090156[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30000689[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]14:20:30[/TD]
[TD][/TD]
[TD]21:56:07[/TD]
[TD][/TD]
[TD]07:35:37[/TD]
[TD][/TD]
[TD]15:11:14[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30001650[/TD]
[TD="align: right"]529[/TD]
[TD][/TD]
[TD="align: right"]195.5638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12:59:46[/TD]
[TD][/TD]
[TD]22:38:03[/TD]
[TD][/TD]
[TD]09:38:17[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]5000092065[/TD]
[TD="align: right"]371[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001650[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]20:59:05[/TD]
[TD][/TD]
[TD]22:38:03[/TD]
[TD][/TD]
[TD]01:38:58[/TD]
[TD][/TD]
[TD]02:42:18[/TD]
[TD][/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]5000091825[/TD]
[TD="align: right"]158[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001650[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]12:59:46[/TD]
[TD][/TD]
[TD]14:03:06[/TD]
[TD][/TD]
[TD]01:03:20[/TD]
[TD][/TD]
[TD]02:42:18[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]30001662[/TD]
[TD="align: right"]757[/TD]
[TD][/TD]
[TD="align: right"]194.9496[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17:06:24[/TD]
[TD][/TD]
[TD]23:25:39[/TD]
[TD][/TD]
[TD]06:19:15[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]5000090338[/TD]
[TD="align: right"]302[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]30001283[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]13:35:50[/TD]
[TD][/TD]
[TD]19:31:56[/TD]
[TD][/TD]
[TD]05:56:06[/TD]
[TD][/TD]
[TD]11:52:12[/TD]
[TD][/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]5000093079[/TD]
[TD="align: right"]455[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30001662[/TD]
[TD][/TD]
[TD]A1S0[/TD]
[TD][/TD]
[TD]19:32:40[/TD]
[TD][/TD]
[TD]23:25:39[/TD]
[TD][/TD]
[TD]03:52:59[/TD]
[TD][/TD]
[TD]03:52:59[/TD]
[TD][/TD]
[TD]117[/TD]
[/TR]
[TR]
[TD]30001668[/TD]
[TD="align: right"]172[/TD]
[TD][/TD]
[TD="align: right"]150.0363[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21:37:46[/TD]
[TD][/TD]
[TD]22:43:56[/TD]
[TD][/TD]
[TD]01:06:10[/TD]
[TD][/TD]
[TD]12:03:32[/TD]
[TD][/TD]
[TD]156[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 3129"]
<tbody>[TR]
[TD]5000090338[/TD]
[TD]177[/TD]
[TD][/TD]
[TD]=IFERROR(B23/D23,"")[/TD]
[TD][/TD]
[TD]=IFERROR(VLOOKUP(A23,Data!$1:$1048576,10,0),"")[/TD]
[TD][/TD]
[TD]=IFERROR(VLOOKUP(A23,Data!$1:$1048576,3,0),"")[/TD]
[TD][/TD]
[TD]=IF(A23<5000000000,"",(MIN(IF(Data!A:A=A23,Data!E:E,IF(Data!J:J=A23,Data!L:L,1)))))[/TD]
[TD][/TD]
[TD]=IFERROR(MAX(IF(Data!A:A=A23,Data!L:L,IF(A23<5000000000,"",IF(Data!J:J=A23,Data!L:L,0)))),"")[/TD]
[TD][/TD]
[TD]=IFERROR(IF(M23-K23>0,M23-K23,""),"")[/TD]
[TD][/TD]
[TD]=SUMIF(G:G,G23,O:O)[/TD]
[TD][/TD]
[TD]=IFERROR(IF(O23>0.20833,B23/((O23-$U$3)*24),B23/(O23*24)),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
These are the pivot table numbers, followed the formulas used in the "Pivot" sheet.
I have put the cells in red that are the main issue here. But if it happens at all it will not give an accurate report. Does anyone know how to find the end time that a picker finished and then the new start time.
Please let me know if you can help.
maybe someone even knows a better way to use a macro to get this information. The formulas really take a while. I dont know how to attach the file on here.
