Spreadsheet: 863506d1710957587-calculating-time-based-on-event-formula-help-tracking-time-on-events
Sheet: Sheet1

AGV Route (Number): | AGV Number: | Battery #1 in | Battery #2 in | Battery #1 out | Battery #2 out | Time stamp | Name |  |  |  |  | Battery #
 | 13 | 229 | 227 | 536 | 528 | 09:45:00 | MonDAGV |  | Explanation:
 | 12 | 25 | AX28 | 451 | 425 | 10:05:00 | MonDAGV |  | AGV 13 had flat batteries. Battery #536 and #528 were removed to put in 229 and 227 |  |  | 18
 | 9 | 425 | 451 | 230 | 442 | 10:15:00 | MonDAGV |  | Some time later |  |  | 25
 | 3 | 238 | 377 | 96 | 106 | 08:30:00 | MonDAGV |  | AGV 13 had flat batteries. Battery # 229 and #227 were removed |  |  | 45
 | 1 | 73 | 444 | 427 | 174 | 07:22:00 | MonDAGV |  |  |  |  | 70
 | 5 | 1055 | 551 | 45 | 107 | 10:20:00 | MonDAGV |  | I need a sum to tell me the different between the time battery 227 went in and when it was removed |  |  | 73
 | 87 | 1060 | 354 | 1015 | 1012 | 09:45:00 | MonDAGV |  |  |  |  | 83
 | 13 | 418 | 94 | 310 | 227 | 12:10:00 | MonDAGV |  | Some time later, 227 will go back in again, and be removed again - I'll need another time on this occasion and the next etc. |  |  | 94
 | 30 | 83 | 657 | 520 | 523 | 12:22:00 | MonDAGV |  |  |  |  | 96
 | 21 | 70 | 528 | 10 | 18 | 12:44:00 | MonDAGV |  |  |  |  | 106
 | 1 | 427 | 174 | 73 | 444 | 13:44:00 | MonDAGV |  | I run a power query to gather all the relevant data for AGV and flat batteries. The 'Name' is the sheet it's pulled from |  |  | 107
 | 5 | 227 | 154 | 258 | 296 | 14:55:00 | MonDAGV |  | but I think these will be sheet numbers from 1-31 for dates vs week day numbers |  |  | 124
 | 5 | 124 | 142 | 227 | 136 | 16:32:00 | MonDAGV |  |  |  |  | 136
 | 9 | 591 | 227 | 227 | 897 | 19:55:00 | MonDAGV |  |  |  |  | 142
 |  |  |  |  |  |  |  |  |  |  |  | 154
 |  |  |  |  |  |  |  |  |  |  |  | 174
 |  | Battery: | Time in: | Time out: | Length of service: |  |  |  |  |  |  | 227
227 |  |  | 09:45:00 | 12:10:00 | 02:25:00 |  |  |  |  |  |  | 229
 |  | 227 | 14:55:00 | 16:32:00 | 01:37:00 |  |  |  |  |  |  | 230
 |  | 227 | 19:55:00 |  |  |  |  |  |  |  |  | 238
 |  |  |  |  |  |  |  |  |  |  |  | 258
 |  |  |  |  |  |  |  |  |  |  |  | 296
 |  |  |  |  |  |  |  |  |  |  |  | 310
 |  |  |  |  |  |  |  |  |  |  |  | 354
 |  | Desired |  |  |  |  |  |  |  |  |  | 377
 |  | Battery: | Time in: | Time out: | Length of service: |  |  |  |  |  |  | 418
 |  | 227 | 09:45:00 | 12:10:00 |  |  |  |  |  |  |  | 425
 |  | 227 | 14:55:00 | 16:32:00 |  |  |  |  |  |  |  | 427
 |  | 227 | 19:55:00 |  |  |  |  |  |  |  |  | 442
 |  |  |  |  |  |  |  |  |  |  |  | 444
 |  |  |  |  |  |  |  |  |  |  |  | 451
 |  |  |  |  |  |  |  |  |  |  |  | 520
 |  |  |  |  |  |  |  |  |  |  |  | 523
 |  |  |  |  |  |  |  |  |  |  |  | 528
 |  |  |  |  |  |  |  |  |  |  |  | 536
 |  |  |  |  |  |  |  |  |  |  |  | 551
 |  |  |  |  |  |  |  |  |  |  |  | 591
 |  |  |  | AGV Number: | [NAME] used | Time stamp |  |  |  |  |  | 657
 |  |  |  |  | 1 | 09:45:00 |  |  |  |  |  | 897
 |  |  |  | 13 | 1 | 12:10:00 |  |  |  |  |  | 1012
 |  |  |  | 5 | 1 | 14:55:00 |  |  |  |  |  | 1015
 |  |  |  | 5 | 1 | 16:32:00 |  |  |  |  |  | 1055
 |  |  |  | 9 | 2 | 19:55:00 |  |  |  |  |  | 1060
 |  |  |  |  |  |  |  |  |  |  |  | AX28