complicated way of dividing data

B

bertusavius

Board Regular
Joined
Feb 28, 2008
Messages
82
[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]I have a table with a list of events that have ID, two time-values and two 'trip values'.



[TABLE="class: grid, width: 200"]
<TBODY>[TR]
[TD="width: 21, bgcolor: #4f81bd"]ID
[/TD]
[TD="width: 66, bgcolor: #4f81bd"]Timestart
[/TD]
[TD="width: 65, bgcolor: #4f81bd"]Timestop
[/TD]
[TD="width: 57, bgcolor: #4f81bd"]tripstart
[/TD]
[TD="width: 56, bgcolor: #4f81bd"]tripstop
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]6:55
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:55
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259010
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:06
[/TD]
[TD="bgcolor: transparent, align: right"]7:55
[/TD]
[TD="bgcolor: transparent, align: right"]259011
[/TD]
[TD="bgcolor: transparent, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:11
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:57
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259013
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:17
[/TD]
[TD="bgcolor: transparent, align: right"]7:57
[/TD]
[TD="bgcolor: transparent, align: right"]259014
[/TD]
[TD="bgcolor: transparent, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:23
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:57
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259015
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:27
[/TD]
[TD="bgcolor: transparent, align: right"]7:59
[/TD]
[TD="bgcolor: transparent, align: right"]259017
[/TD]
[TD="bgcolor: transparent, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:29
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:58
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259017
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:40
[/TD]
[TD="bgcolor: transparent, align: right"]7:59
[/TD]
[TD="bgcolor: transparent, align: right"]259025
[/TD]
[TD="bgcolor: transparent, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:11
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:21
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259038
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259043
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]8:41
[/TD]
[TD="bgcolor: transparent, align: right"]9:07
[/TD]
[TD="bgcolor: transparent, align: right"]259049
[/TD]
[TD="bgcolor: transparent, align: right"]259063
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:48
[/TD]
[TD="bgcolor: #dce6f1, align: right"]9:16
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259054
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259064
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:24
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:34
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259116
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259122
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]13:40
[/TD]
[TD="bgcolor: transparent, align: right"]14:14
[/TD]
[TD="bgcolor: transparent, align: right"]259122
[/TD]
[TD="bgcolor: transparent, align: right"]259134
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:45
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:22
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259124
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]13:54
[/TD]
[TD="bgcolor: transparent, align: right"]14:22
[/TD]
[TD="bgcolor: transparent, align: right"]259127
[/TD]
[TD="bgcolor: transparent, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:03
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:23
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259130
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]14:35
[/TD]
[TD="bgcolor: transparent, align: right"]14:58
[/TD]
[TD="bgcolor: transparent, align: right"]259144
[/TD]
[TD="bgcolor: transparent, align: right"]259148
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:36
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:58
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259144
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259148
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]14:43
[/TD]
[TD="bgcolor: transparent, align: right"]15:06
[/TD]
[TD="bgcolor: transparent, align: right"]259145
[/TD]
[TD="bgcolor: transparent, align: right"]259150
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:52
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:38
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259146
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259162
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]15:19
[/TD]
[TD="bgcolor: transparent, align: right"]15:42
[/TD]
[TD="bgcolor: transparent, align: right"]259155
[/TD]
[TD="bgcolor: transparent, align: right"]259163
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:27
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:47
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259157
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259164
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]15:54
[/TD]
[TD="bgcolor: transparent, align: right"]16:02
[/TD]
[TD="bgcolor: transparent, align: right"]259165
[/TD]
[TD="bgcolor: transparent, align: right"]259167
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]To make things less abstract:
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]The trip-value is the value of a trip meter in a car in kilometers.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]So the top record actualy says:
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]car nr 25 had an event that started at 06:55 at trip value 259010 and this event stopped at 07:55 coinciding with trip value 259030
You could say this is a list of events with corresponding accumulating properties, so wether the timeID starts or stops doesnt really matter. All the matters is that a certain time corresponds with a certain trip value.




[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]Now wat I'd like is to create a measure that transforms and divides this information like this:



[TABLE="class: grid, width: 193"]
<TBODY>[TR]
[TD="width: 62, bgcolor: #4f81bd"]ID
[/TD]
[TD="width: 80, bgcolor: #4f81bd"]binID
[/TD]
[TD="width: 115, bgcolor: #4f81bd"]distance travelled
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1"]0700-0800
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent"]0800-0900
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1"]0900-1000
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent"]0700-0800
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1"]0800-0900
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent"]0900-1000
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 341"]
<TBODY>[TR]
[TD="width: 455, bgcolor: transparent"]I have a table to relate to which contains binID, binstart and binstop
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I also have a table for dates
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Is it even worth contemplating to solve this in Powerpivot, because it seems quite daunting to me atm.


[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
I'm running your code very succesfully on my data.

But would you mind taking a look at this data:

[TABLE="width: 336"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: #4f81bd"]Datum
[/TD]
[TD="class: xl66, width: 64, bgcolor: #4f81bd"]TX start
[/TD]
[TD="class: xl66, width: 64, bgcolor: #4f81bd"]TX stop
[/TD]
[TD="class: xl66, width: 64, bgcolor: #4f81bd"]Ch
[/TD]
[TD="class: xl66, width: 64, bgcolor: #4f81bd"]WP_WAGEN_NUMMER
[/TD]
[TD="class: xl66, width: 64, bgcolor: #4f81bd"]INS_KILOMETERSTAND
[/TD]
[TD="class: xl67, width: 64, bgcolor: #4f81bd"]UIT_KILOMETERSTAND
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]7:31:00
[/TD]
[TD="class: xl69, bgcolor: #dce6f1, align: right"]8:32:00
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1006
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225421
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225447
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]7:42
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]8:20
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1006
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225423
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225440
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]7:49
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]8:20
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1006
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225426
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225440
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]7:54
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]8:20
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1006
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225428
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225440
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]8:20
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]8:20
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1006
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225440
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225440
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]9:07:00
[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]10:43:00
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]53
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225447
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225564
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]9:16
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]10:02
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]53
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225451
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225509
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]15:00:00
[/TD]
[TD="class: xl77, bgcolor: transparent, align: right"]15:50:00
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1006
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225564
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225591
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]15:12
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]15:36
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1006
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225572
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225586
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]15:12
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]15:30
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1006
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225572
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225584
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: #dce6f1, align: right"]5-3-2012
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]15:12
[/TD]
[TD="class: xl76, bgcolor: #dce6f1, align: right"]15:27
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1006
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]1
[/TD]
[TD="class: xl70, bgcolor: #dce6f1, align: right"]225572
[/TD]
[TD="class: xl71, bgcolor: #dce6f1, align: right"]225582
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]5-3-2012
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]15:12
[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]15:42
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1006
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]225572
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]225588
[/TD]
[/TR]
</TBODY>[/TABLE]


It seems that two overlapping shifts occur in the output, so the mileages add up while they shouldnt.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
The Code I develope does not look at "Shifts" for each car.... Instead it looks at the Time and the respective mileage at that time for each car.

Here is a different represetation of the data you provided for Car 1006


[TABLE="width: 196"]
<TBODY>[TR]
[TD]Date</SPAN>[/TD]
[TD]Miles </SPAN>[/TD]
[TD]Differenc</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 7:31</SPAN>[/TD]
[TD="align: right"]225421</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 7:42</SPAN>[/TD]
[TD="align: right"]225423</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 7:49</SPAN>[/TD]
[TD="align: right"]225426</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 7:54</SPAN>[/TD]
[TD="align: right"]225428</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:20</SPAN>[/TD]
[TD="align: right"]225440</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:20</SPAN>[/TD]
[TD="align: right"]225440</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:20</SPAN>[/TD]
[TD="align: right"]225440</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:20</SPAN>[/TD]
[TD="align: right"]225440</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:20</SPAN>[/TD]
[TD="align: right"]225440</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 8:32</SPAN>[/TD]
[TD="align: right"]225447</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:00</SPAN>[/TD]
[TD="align: right"]225564</SPAN>[/TD]
[TD="align: right"]117</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:12</SPAN>[/TD]
[TD="align: right"]225572</SPAN>[/TD]
[TD="align: right"]8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:12</SPAN>[/TD]
[TD="align: right"]225572</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:12</SPAN>[/TD]
[TD="align: right"]225572</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:12</SPAN>[/TD]
[TD="align: right"]225572</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:27</SPAN>[/TD]
[TD="align: right"]225582</SPAN>[/TD]
[TD="align: right"]10</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:30</SPAN>[/TD]
[TD="align: right"]225584</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:36</SPAN>[/TD]
[TD="align: right"]225586</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:42</SPAN>[/TD]
[TD="align: right"]225588</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012 15:50</SPAN>[/TD]
[TD="align: right"]225591</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total</SPAN>[/TD]
[TD="align: right"]170</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>[/TABLE]


I am using the Difference (Column C) to do the Calculations.... Based on my understanding of your requirement, The 'Shift' Really does not have anything to do with the calculation, it's really a snap shot of miles at a specific time that matters.

Am I missing something?:confused:
 
Upvote 0
I think I understand what your method of mileage calculation per hour is, but in the output you seem to correctly relate this to car and driver ID. However, sometimes this relation doenst work like in the above example.

I'm really looking for an absolute relation between mileage-carID-DriverID.

BTW: Are you aware that value 1006 represents a driver ID (not car)?

This would be an explanation of the data:

[TABLE="width: 1087"]
<tbody>[TR]
[TD]date[/TD]
[TD]startevent[/TD]
[TD]stopevent[/TD]
[TD]DRIVER[/TD]
[TD]CAR[/TD]
[TD]startmileage[/TD]
[TD]stopmileage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Datum[/TD]
[TD]TX start[/TD]
[TD]TX stop[/TD]
[TD]Ch[/TD]
[TD]WP_WAGEN_NUMMER[/TD]
[TD]INS_KILOMETERSTAND[/TD]
[TD="colspan: 3"]UIT_KILOMETERSTAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]7:31:00[/TD]
[TD="align: right"]8:32:00[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225421[/TD]
[TD="align: right"]225447[/TD]
[TD][/TD]
[TD="colspan: 9"]Driver 1006 starts his shift in car 1 at 07:31 and stops his shift at 08:32 with associated mileages[/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]7:42[/TD]
[TD="align: right"]8:20[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225423[/TD]
[TD="align: right"]225440[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]7:49[/TD]
[TD="align: right"]8:20[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225426[/TD]
[TD="align: right"]225440[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]7:54[/TD]
[TD="align: right"]8:20[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225428[/TD]
[TD="align: right"]225440[/TD]
[TD][/TD]
[TD="colspan: 8"]Driver 1006 starts a job at 07:54 and stops at at 08:20 with associated mileages[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]8:20[/TD]
[TD="align: right"]8:20[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225440[/TD]
[TD="align: right"]225440[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]9:07:00[/TD]
[TD="align: right"]10:43:00[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225447[/TD]
[TD="align: right"]225564[/TD]
[TD][/TD]
[TD="colspan: 9"]Driver 53 starts his shift in car 1 at 09:07 and stops his shift at 10:43 with associated mileages[/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]9:16[/TD]
[TD="align: right"]10:02[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225451[/TD]
[TD="align: right"]225509[/TD]
[TD][/TD]
[TD="colspan: 8"]Driver 53 starts a job at 9:16 and stops it at 10:02 with associated mileages[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]15:00:00[/TD]
[TD="align: right"]15:50:00[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225564[/TD]
[TD="align: right"]225591[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:36[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225572[/TD]
[TD="align: right"]225586[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:30[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225572[/TD]
[TD="align: right"]225584[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:27[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225572[/TD]
[TD="align: right"]225582[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-3-2012[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:42[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]225572[/TD]
[TD="align: right"]225588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When you use this next piece data data for example, you see that only in the first hour there is no 'overlap':

[TABLE="width: 443"]
<tbody>[TR]
[TD]Datum[/TD]
[TD]TX start[/TD]
[TD]TX stop[/TD]
[TD]Ch[/TD]
[TD]WP_WAGEN_NUMMER[/TD]
[TD]INS_KILOMETERSTAND[/TD]
[TD]UIT_KILOMETERSTAND[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]8:25:00[/TD]
[TD="align: right"]12:55:00[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200063[/TD]
[TD="align: right"]200353[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]8:41[/TD]
[TD="align: right"]9:16[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200077[/TD]
[TD="align: right"]200106[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]8:53[/TD]
[TD="align: right"]9:05[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200087[/TD]
[TD="align: right"]200097[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]8:58[/TD]
[TD="align: right"]9:13[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200091[/TD]
[TD="align: right"]200103[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]10:12[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200094[/TD]
[TD="align: right"]200170[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]9:36[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200094[/TD]
[TD="align: right"]200127[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:04[/TD]
[TD="align: right"]9:28[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200096[/TD]
[TD="align: right"]200119[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:09[/TD]
[TD="align: right"]9:21[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200098[/TD]
[TD="align: right"]200110[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:20[/TD]
[TD="align: right"]9:28[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200109[/TD]
[TD="align: right"]200119[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:36[/TD]
[TD="align: right"]10:02[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200127[/TD]
[TD="align: right"]200160[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:40[/TD]
[TD="align: right"]10:19[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200130[/TD]
[TD="align: right"]200181[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:40[/TD]
[TD="align: right"]10:19[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200130[/TD]
[TD="align: right"]200182[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]9:48[/TD]
[TD="align: right"]9:58[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200136[/TD]
[TD="align: right"]200155[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]10:12[/TD]
[TD="align: right"]10:38[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200170[/TD]
[TD="align: right"]200208[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]10:12[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200170[/TD]
[TD="align: right"]200208[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]10:30[/TD]
[TD="align: right"]10:41[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200195[/TD]
[TD="align: right"]200208[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]10:47[/TD]
[TD="align: right"]10:53[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200210[/TD]
[TD="align: right"]200216[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]10:59[/TD]
[TD="align: right"]11:17[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200217[/TD]
[TD="align: right"]200254[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]11:42[/TD]
[TD="align: right"]11:53[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200296[/TD]
[TD="align: right"]200307[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]11:42[/TD]
[TD="align: right"]11:54[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200296[/TD]
[TD="align: right"]200307[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]11:43[/TD]
[TD="align: right"]12:39[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200296[/TD]
[TD="align: right"]200340[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]11:49[/TD]
[TD="align: right"]12:20[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200304[/TD]
[TD="align: right"]200330[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]12:09[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200318[/TD]
[TD="align: right"]200336[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]12:19[/TD]
[TD="align: right"]12:33[/TD]
[TD="align: right"]5004[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200330[/TD]
[TD="align: right"]200338[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]12:58:00[/TD]
[TD="align: right"]21:54:00[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200353[/TD]
[TD="align: right"]200959[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:18[/TD]
[TD="align: right"]13:23[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200378[/TD]
[TD="align: right"]200383[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200383[/TD]
[TD="align: right"]200388[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200383[/TD]
[TD="align: right"]200388[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200383[/TD]
[TD="align: right"]200388[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200383[/TD]
[TD="align: right"]200388[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]13:53[/TD]
[TD="align: right"]14:17[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200398[/TD]
[TD="align: right"]200437[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]14:03[/TD]
[TD="align: right"]14:23[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200413[/TD]
[TD="align: right"]200445[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]14:04[/TD]
[TD="align: right"]14:23[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200413[/TD]
[TD="align: right"]200445[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]14:39[/TD]
[TD="align: right"]15:08[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200461[/TD]
[TD="align: right"]200506[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]14:48[/TD]
[TD="align: right"]15:01[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200470[/TD]
[TD="align: right"]200500[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]15:06[/TD]
[TD="align: right"]15:29[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200504[/TD]
[TD="align: right"]200524[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]15:12[/TD]
[TD="align: right"]15:22[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200507[/TD]
[TD="align: right"]200511[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]15:22[/TD]
[TD="align: right"]15:37[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200511[/TD]
[TD="align: right"]200535[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]16:29[/TD]
[TD="align: right"]16:43[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200560[/TD]
[TD="align: right"]200575[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]16:55[/TD]
[TD="align: right"]17:16[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200580[/TD]
[TD="align: right"]200641[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]17:16[/TD]
[TD="align: right"]17:30[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200641[/TD]
[TD="align: right"]200671[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]17:52[/TD]
[TD="align: right"]17:58[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200684[/TD]
[TD="align: right"]200690[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]18:19[/TD]
[TD="align: right"]18:47[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200731[/TD]
[TD="align: right"]200767[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]18:31[/TD]
[TD="align: right"]19:16[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200746[/TD]
[TD="align: right"]200797[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]18:44[/TD]
[TD="align: right"]18:54[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200764[/TD]
[TD="align: right"]200777[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]19:09[/TD]
[TD="align: right"]19:20[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200790[/TD]
[TD="align: right"]200801[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]19:09[/TD]
[TD="align: right"]19:21[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200790[/TD]
[TD="align: right"]200803[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:04[/TD]
[TD="align: right"]20:18[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200821[/TD]
[TD="align: right"]200841[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:27[/TD]
[TD="align: right"]20:40[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200849[/TD]
[TD="align: right"]200880[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:57[/TD]
[TD="align: right"]21:21[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200924[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:57[/TD]
[TD="align: right"]21:21[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200924[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:57[/TD]
[TD="align: right"]21:21[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200924[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:57[/TD]
[TD="align: right"]21:22[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200925[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:57[/TD]
[TD="align: right"]21:21[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200924[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:58[/TD]
[TD="align: right"]21:22[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200925[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]20:58[/TD]
[TD="align: right"]21:22[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200901[/TD]
[TD="align: right"]200925[/TD]
[/TR]
[TR]
[TD="align: right"]1-3-2012[/TD]
[TD="align: right"]21:04[/TD]
[TD="align: right"]21:10[/TD]
[TD="align: right"]5336[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200904[/TD]
[TD="align: right"]200913[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Very interesting. I understand the issue and I need to take a closer look at it. I was creating the list based on an Key consisting of Car and Driver. the list, as I showed previouly just constisted of Time and Miles. Once the sorted lists were created for each "Key", I world move thru each list to seperate the miles in Hourly to buckets. It is now clear that I have create the List based on Cars. When I pocess each of the Car List, I need to apply special logic if the driver changes. Do this sound right?
 
Upvote 0
This following code accounts for the change in drivers for a single car...

The Code produces the follow results for the data you provided

[TABLE="width: 339"]
<TBODY>[TR]
[TD]Datum</SPAN>[/TD]
[TD]Ch</SPAN>[/TD]
[TD]WP_Wagen</SPAN>[/TD]
[TD]Hr</SPAN>[/TD]
[TD]Km</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012</SPAN>[/TD]
[TD="align: right"]1006</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]07:00 ~ 07:59</SPAN>[/TD]
[TD="align: right"]10</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012</SPAN>[/TD]
[TD="align: right"]1006</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]08:00 ~ 08:59</SPAN>[/TD]
[TD="align: right"]16</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012</SPAN>[/TD]
[TD="align: right"]1006</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]15:00 ~ 15:59</SPAN>[/TD]
[TD="align: right"]27</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]09:00 ~ 09:59</SPAN>[/TD]
[TD="align: right"]59</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2012</SPAN>[/TD]
[TD="align: right"]53</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]10:00 ~ 10:59</SPAN>[/TD]
[TD="align: right"]58</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL><COL></COLGROUP>[/TABLE]




Code: 
Option Explicit
Const DateCol As Integer = 1
Const StartTimeCol As Integer = 2
Const EndTimeCol As Integer = 3
Const DriverCol As Integer = 4
Const CarNoCol As Integer = 5
Const StartMileCol As Integer = 6
Const EndMileCol As Integer = 7
Type typDt_MilesRec
    Driver As Integer
    Dt As Date
    Miles As Long
End Type
Type typMstRec
    Car As Integer
    DT_Miles() As typDt_MilesRec
End Type
Type typBin
    Dt_Hr As Date
    Miles As Long
End Type
Type typDriver
    Driver As Integer
    Bin() As typBin
End Type
Sub Process()
    Dim ws As Worksheet
    Dim ws3 As Worksheet
    
    Dim MstRec() As typMstRec
    Dim RowNo As Long
    Dim LastRow As Long
    
    Dim I As Long
    Dim Car As Integer
    Dim Driver As Integer
    Dim CarIdx As Integer
    
    Dim tempStartTime As Date
    
    ReDim MstRec(0)
    
    Set ws = ThisWorkbook.Worksheets(1)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For RowNo = 2 To LastRow
        If Len(Trim(ws.Cells(RowNo, StartTimeCol))) > 1 Then
            Car = Trim(ws.Cells(RowNo, CarNoCol))
            Driver = Trim(ws.Cells(RowNo, DriverCol))
            CarIdx = FindCarIdx(Car, MstRec)
            
            I = UBound(MstRec(CarIdx).DT_Miles) + 1
            ReDim Preserve MstRec(CarIdx).DT_Miles(I)
            
            MstRec(CarIdx).DT_Miles(I).Driver = Driver
            MstRec(CarIdx).DT_Miles(I).Miles = Val(ws.Cells(RowNo, StartMileCol))
            MstRec(CarIdx).DT_Miles(I).Dt = CDate(ws.Cells(RowNo, DateCol) & " " & CDate(ws.Cells(RowNo, StartTimeCol)))
            tempStartTime = MstRec(CarIdx).DT_Miles(I).Dt
            Call InsertRec(MstRec, CarIdx)
            
            I = UBound(MstRec(CarIdx).DT_Miles) + 1
            ReDim Preserve MstRec(CarIdx).DT_Miles(I)
            
            MstRec(CarIdx).DT_Miles(I).Driver = Driver
            MstRec(CarIdx).DT_Miles(I).Miles = Val(ws.Cells(RowNo, EndMileCol))
            MstRec(CarIdx).DT_Miles(I).Dt = CDate(ws.Cells(RowNo, DateCol) & " " & CDate(ws.Cells(RowNo, EndTimeCol)))
            If tempStartTime > MstRec(CarIdx).DT_Miles(I).Dt Then
                MstRec(CarIdx).DT_Miles(I).Dt = DateAdd("D", 1, MstRec(CarIdx).DT_Miles(I).Dt)
            End If
            Call InsertRec(MstRec, CarIdx)
        End If
    Next RowNo
    
    Call OutputAll(MstRec)
    
    MsgBox "Complete", vbInformation
End Sub
Function InsertRec(Rec() As typMstRec, CarIdx As Integer)
    Dim I As Long
    Dim xRec As typDt_MilesRec
    Dim MaxIdx As Integer
    
    MaxIdx = UBound(Rec(CarIdx).DT_Miles)
    
    '*****  This function sorts the last record into the correct location
    For I = MaxIdx - 1 To 1 Step -1
        If Rec(CarIdx).DT_Miles(I).Dt > Rec(CarIdx).DT_Miles(I + 1).Dt Then
            xRec = Rec(CarIdx).DT_Miles(I + 1)
            Rec(CarIdx).DT_Miles(I + 1) = Rec(CarIdx).DT_Miles(I)
            Rec(CarIdx).DT_Miles(I) = xRec
        Else
            Exit Function
        End If
    Next I
End Function
Function OutputAll(MstRec() As typMstRec)
    Dim ws As Worksheet
    Dim RowNo  As Integer
    Dim CarIdx As Integer
    
    Dim v As Variant
    Dim I As Integer
    
    Set ws = ThisWorkbook.Worksheets(2)
    ws.Cells.ClearContents
    RowNo = 1
    
    ws.Cells(RowNo, 1) = "Datum"
    ws.Cells(RowNo, 2) = "Ch"
    ws.Cells(RowNo, 3) = "WP_Wagen"
    ws.Cells(RowNo, 4) = "Hr"
    ws.Cells(RowNo, 5) = "Km"
    
    For CarIdx = 1 To UBound(MstRec)
        Call OutputData(ws, MstRec(CarIdx), MstRec(CarIdx).Car)
        'Debug.Print MstRec(CarIdx).Car
    Next CarIdx
End Function
Function OutputData(ws As Worksheet, MstRec As typMstRec, Car As Integer)
    Dim I As Integer
    Dim RowNo As Long
    
    Dim v As Variant
    Dim J As Integer
    
    Dim Perc As Single
    Dim tempMins As Integer
    Dim tempMiles As Long
    
    Dim ElapsedMins As Integer
    Dim ElapsedMiles As Long
    
    Dim StartTime As Date
    Dim EndTime As Date
    
    Dim DriverIdx As Integer
    Dim BinIdx As Integer
    Dim StartHr As Integer
    Dim EndHr As Integer
    Dim HourDiff As Integer
    
    Dim tempTime As Date
    
    Dim Drivers() As typDriver
    ReDim Drivers(0)
    ReDim Drivers(0).Bin(0)
    
    RowNo = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
    For I = 2 To UBound(MstRec.DT_Miles)
        If MstRec.DT_Miles(I).Driver <> MstRec.DT_Miles(I - 1).Driver Then
            GoTo NextRec
        End If
        
        DriverIdx = FindDriverIdx(MstRec.DT_Miles(I).Driver, Drivers)
        
        StartTime = MstRec.DT_Miles(I - 1).Dt
        EndTime = MstRec.DT_Miles(I).Dt
        ElapsedMiles = MstRec.DT_Miles(I).Miles - MstRec.DT_Miles(I - 1).Miles
        
        If ElapsedMiles > 0 Then
            ElapsedMins = DateDiff("n", MstRec.DT_Miles(I - 1).Dt, MstRec.DT_Miles(I).Dt)
            HourDiff = DateDiff("h", StartTime, EndTime)
            Select Case HourDiff
                Case 0
                    BinIdx = FindBin_DT_HR(StartTime, Drivers(DriverIdx).Bin)
                    Drivers(DriverIdx).Bin(BinIdx).Miles = Drivers(DriverIdx).Bin(BinIdx).Miles + ElapsedMiles
                Case Else
                    '***** Determine fractional time for 1st hour
                    BinIdx = FindBin_DT_HR(StartTime, Drivers(DriverIdx).Bin)
                    tempTime = CDate(Format(StartTime, "MM/DD/YY HH:00:00"))
                    tempMins = DateDiff("n", StartTime, DateAdd("h", 1, tempTime))
                    Perc = tempMins / ElapsedMins
                    Drivers(DriverIdx).Bin(BinIdx).Miles = Drivers(DriverIdx).Bin(BinIdx).Miles + (ElapsedMiles * Perc)
                    
                    'Determine Full hour mileage between Start and End Time
                    Perc = 60 / ElapsedMins
                    tempTime = DateAdd("h", 1, StartTime)
                    Do While Hour(tempTime) < Hour(EndTime)
                        BinIdx = FindBin_DT_HR(tempTime, Drivers(DriverIdx).Bin)
                        Drivers(DriverIdx).Bin(BinIdx).Miles = Drivers(DriverIdx).Bin(BinIdx).Miles + (ElapsedMiles * Perc)
                        tempTime = DateAdd("h", 1, tempTime)
                    Loop
                    
                    '***** Determine fractional time for Last hour
                    BinIdx = FindBin_DT_HR(EndTime, Drivers(DriverIdx).Bin)
                    tempTime = CDate(Format(EndTime, "MM/DD/YY HH:00:00"))
                    tempMins = DateDiff("n", tempTime, EndTime)
                    Perc = tempMins / ElapsedMins
                    Drivers(DriverIdx).Bin(BinIdx).Miles = Drivers(DriverIdx).Bin(BinIdx).Miles + (ElapsedMiles * Perc)
            End Select
        End If
        
NextRec:
    Next I
    
    For DriverIdx = 1 To UBound(Drivers)
    
        For I = 1 To UBound(Drivers(DriverIdx).Bin)
            ws.Cells(RowNo, 1) = CDate(Format(Drivers(DriverIdx).Bin(I).Dt_Hr, "MM/DD/YY"))
            ws.Cells(RowNo, 2) = Drivers(DriverIdx).Driver
            ws.Cells(RowNo, 3) = Car
            
            ws.Cells(RowNo, 4) = Format(Drivers(DriverIdx).Bin(I).Dt_Hr, "HH:MM") & " ~ " & Format(Drivers(DriverIdx).Bin(I).Dt_Hr, "HH:59")
            ws.Cells(RowNo, "E") = Drivers(DriverIdx).Bin(I).Miles
            RowNo = RowNo + 1
        Next I
    Next DriverIdx
End Function
Function FindBin_DT_HR(ByVal Dt As Date, Rec() As typBin) As Integer
    Dim I As Integer
    Dim tempDT As Date
    
    tempDT = Format(Dt, "MM/DD/YY HH:00:00")
    
    For I = 1 To UBound(Rec)
        If tempDT = Rec(I).Dt_Hr Then
            FindBin_DT_HR = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).Dt_Hr = tempDT
    
    FindBin_DT_HR = I
End Function
Function FindDriverIdx(ByVal Driver As String, Drivers() As typDriver) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Drivers)
        If Driver = Drivers(I).Driver Then
            FindDriverIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Drivers(I)
    Drivers(I).Driver = Driver
    
    ReDim Preserve Drivers(I).Bin(0)
    FindDriverIdx = I
End Function
Function FindCarIdx(ByVal Car As String, Rec() As typMstRec) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Rec)
        If Car = Rec(I).Car Then
            FindCarIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).Car = Car
    
    ReDim Preserve Rec(I).DT_Miles(0)
    
    FindCarIdx = I
End Function
 
Upvote 0
By the way you explain it, I think you get the essence.
The output is also exactly what I'm looking for.

(I'm running this code on a different machine than usual (I don't know if that matters)) but I receive an overflow error.

This line is highlighted:
tempMins = DateDiff("n", StartTime, DateAdd("h", 1, tempTime))

I treid redefining Dim tempMins As Integer to no avail
 
Upvote 0
this error beacuse the Minutes difference between StartTime and TempTime exceeds the Size of an Interger.

What Data Set are you using?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
196
shansakhi
S
K
  • Question Question
Help with looping through an entire lists in Power Query
Replies
2
Views
519
JGordon11
JGordon11
jimrward
  • Solved
Process times
Replies
6
Views
425
JEC
JEC
R
  • Question Question
Using Power query for appending data from table to customized output
Replies
2
Views
290
alansidman
A
J
  • Solved
Missy Record from Power Query
Replies
4
Views
473
jarett
J

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

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