complicated way of dividing data

bertusavius · Sep 24, 2012

[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]

Laurent C · Sep 26, 2012

bertusavius said:
@mr C:
interesting approach. As long as the intervals are small and there are many events per hour, the averages work quite well.
But I'm affraid they're not accurate enough.

I'd like the cumulative values of the buckets to be as close as or equal to the real trip data.

I did not mean using an average, but using a calculated table within a SUMX function. I used linear extrapolation for the calculation, but you could use some more elaborate formula, if required.

If you have an event that spans over 3 time buckets, then you would have 3 rows in your calculated table, each with its extrapolated values for tripstart and tripstop.

b.downey · Sep 26, 2012

The Bin Data provided is not cummulative from One Bin two the Next (One hour to the next).... The Data in each bin is prorated for that Bin.

Here is the results of the data from your example.

As you see, the data from 8:00 AM to 8:59 AM (19.5) is less that that from the 7-8 AM hour (119.5)

Am I missing something

[TABLE="class: grid, width: 219, align: center"]
<TBODY>[TR]
[TD]ID</SPAN>[/TD]
[TD]BIN</SPAN>[/TD]
[TD] Distance Traveled </SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]6</SPAN>[/TD]
[TD] 1.7 </SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[TD] 119.3 </SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]8</SPAN>[/TD]
[TD] 19.5 </SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[TD] 9.5 </SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL></COLGROUP>[/TABLE]

bertusavius · Sep 26, 2012

Sorry for the delay.
I tested both your solutions.
They come up with exactly the same results.

If I use it on this different data for instance:

	A	B	C	D

<tbody>
[TD="align: center"]32[/TD]
[TD="bgcolor: #4F81BD"]TX start[/TD]
[TD="bgcolor: #4F81BD"]TX stop[/TD]
[TD="bgcolor: #4F81BD"]INS_KILOMETERSTAND[/TD]
[TD="bgcolor: #4F81BD"]UIT_KILOMETERSTAND[/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #DCE6F1, align: right"]15:18[/TD]
[TD="bgcolor: #DCE6F1, align: right"]15:23[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272690[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272692
[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: right"]15:59[/TD]
[TD="align: right"]272690[/TD]
[TD="align: right"]272712[/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #DCE6F1, align: right"]15:33[/TD]
[TD="bgcolor: #DCE6F1, align: right"]15:44[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272698[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272705[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]15:33[/TD]
[TD="align: right"]15:47[/TD]
[TD="align: right"]272698[/TD]
[TD="align: right"]272706[/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16:16[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16:25[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272716[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272718[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]16:38[/TD]
[TD="align: right"]16:47[/TD]
[TD="align: right"]272720[/TD]
[TD="align: right"]272724[/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16:52[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16:57[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272725[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272728[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]16:20[/TD]
[TD="align: right"]16:37[/TD]
[TD="align: right"]272716[/TD]
[TD="align: right"]272720[/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16:59[/TD]
[TD="bgcolor: #DCE6F1, align: right"]17:12[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272728[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272731[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]17:31[/TD]
[TD="align: right"]17:32[/TD]
[TD="align: right"]272734[/TD]
[TD="align: right"]272734[/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #DCE6F1, align: right"]17:24[/TD]
[TD="bgcolor: #DCE6F1, align: right"]17:31[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272731[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272734[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]17:44[/TD]
[TD="align: right"]17:56[/TD]
[TD="align: right"]272737[/TD]
[TD="align: right"]272742[/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #DCE6F1, align: right"]17:37[/TD]
[TD="bgcolor: #DCE6F1, align: right"]18:05[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272735[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272745[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]18:54[/TD]
[TD="align: right"]19:19[/TD]
[TD="align: right"]272762[/TD]
[TD="align: right"]272771[/TD]

[TD="align: center"]47[/TD]
[TD="bgcolor: #DCE6F1, align: right"]18:25[/TD]
[TD="bgcolor: #DCE6F1, align: right"]19:10[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272751[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272768[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]18:13[/TD]
[TD="align: right"]18:18[/TD]
[TD="align: right"]272747[/TD]
[TD="align: right"]272748[/TD]

[TD="align: center"]49[/TD]
[TD="bgcolor: #DCE6F1, align: right"]18:50[/TD]
[TD="bgcolor: #DCE6F1, align: right"]19:49[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272761[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272794[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]18:21[/TD]
[TD="align: right"]18:38[/TD]
[TD="align: right"]272749[/TD]
[TD="align: right"]272755[/TD]

[TD="align: center"]51[/TD]
[TD="bgcolor: #DCE6F1, align: right"]19:02[/TD]
[TD="bgcolor: #DCE6F1, align: right"]19:15[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272763[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272770[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]20:15[/TD]
[TD="align: right"]20:31[/TD]
[TD="align: right"]272811[/TD]
[TD="align: right"]272821[/TD]

[TD="align: center"]53[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20:44[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20:51[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272832[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272834[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]21:34[/TD]
[TD="align: right"]21:40[/TD]
[TD="align: right"]272836[/TD]
[TD="align: right"]272839[/TD]

[TD="align: center"]55[/TD]
[TD="bgcolor: #DCE6F1, align: right"]21:40[/TD]
[TD="bgcolor: #DCE6F1, align: right"]21:47[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272839[/TD]
[TD="bgcolor: #DCE6F1, align: right"]272841[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]22:24[/TD]
[TD="align: right"]272850[/TD]
[TD="align: right"]272868[/TD]

</tbody>

Blad1

this is table 'data'

with this table 'buckets'

	A	E	F

<tbody>
[TD="align: center"]35[/TD]
[TD="bgcolor: #4F81BD"]BucketID[/TD]
[TD="bgcolor: #4F81BD"]Start tijd[/TD]
[TD="bgcolor: #4F81BD"]Eind tijd[/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2000300[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 2:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 3:00[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]3000400[/TD]
[TD="align: right"]0-01-00 3:00[/TD]
[TD="align: right"]0-01-00 4:00[/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #DCE6F1, align: right"]4000500[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 4:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 5:00[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]5000600[/TD]
[TD="align: right"]0-01-00 5:00[/TD]
[TD="align: right"]0-01-00 6:00[/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #DCE6F1, align: right"]6000700[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 6:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 7:00[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]7000800[/TD]
[TD="align: right"]0-01-00 7:00[/TD]
[TD="align: right"]0-01-00 8:00[/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #DCE6F1, align: right"]8000900[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 8:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 9:00[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]9001000[/TD]
[TD="align: right"]0-01-00 9:00[/TD]
[TD="align: right"]0-01-00 10:00[/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #DCE6F1, align: right"]10001100[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 10:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 11:00[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]11001200[/TD]
[TD="align: right"]0-01-00 11:00[/TD]
[TD="align: right"]0-01-00 12:00[/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #DCE6F1, align: right"]12001300[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 12:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 13:00[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]13001400[/TD]
[TD="align: right"]0-01-00 13:00[/TD]
[TD="align: right"]0-01-00 14:00[/TD]

[TD="align: center"]48[/TD]
[TD="bgcolor: #DCE6F1, align: right"]14001500[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 14:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 15:00[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]15001600[/TD]
[TD="align: right"]0-01-00 15:00[/TD]
[TD="align: right"]0-01-00 16:00[/TD]

[TD="align: center"]50[/TD]
[TD="bgcolor: #DCE6F1, align: right"]16001700[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 16:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 17:00[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]17001800[/TD]
[TD="align: right"]0-01-00 17:00[/TD]
[TD="align: right"]0-01-00 18:00[/TD]

[TD="align: center"]52[/TD]
[TD="bgcolor: #DCE6F1, align: right"]18001900[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 18:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 19:00[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]19002000[/TD]
[TD="align: right"]0-01-00 19:00[/TD]
[TD="align: right"]0-01-00 20:00[/TD]

[TD="align: center"]54[/TD]
[TD="bgcolor: #DCE6F1, align: right"]20002100[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 20:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 21:00[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]21002200[/TD]
[TD="align: right"]0-01-00 21:00[/TD]
[TD="align: right"]0-01-00 22:00[/TD]

[TD="align: center"]56[/TD]
[TD="bgcolor: #DCE6F1, align: right"]22002300[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 22:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 23:00[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]23002400[/TD]
[TD="align: right"]0-01-00 23:00[/TD]
[TD="align: right"]0-01-00 0:00[/TD]

[TD="align: center"]58[/TD]
[TD="bgcolor: #DCE6F1, align: right"]24002500[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 0:00[/TD]
[TD="bgcolor: #DCE6F1, align: right"]0-01-00 1:00[/TD]

[TD="align: center"]59[/TD]
[TD="align: right"]25002600[/TD]
[TD="align: right"]0-01-00 1:00[/TD]
[TD="align: right"]0-01-00 2:00[/TD]

[TD="align: center"]60[/TD]
[TD="bgcolor: #DCE6F1, align: right"]24002500[/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]
[TD="bgcolor: #DCE6F1, align: right"][/TD]

</tbody>

Blad2

Als using a calculated column estimated speed
=([UIT_KILOMETERSTAND]-[INS_KILOMETERSTAND])/(([TX stop]-[TX start])*24)

with a measure:
SUMX(
CROSSJOIN('data';'buckets');
IF(AND('data'[TX start] < 'buckets'[Eind tijd] ; 'data'[TX stop] > 'buckets'[Start tijd])
; if('buckets'[Eind tijd] > data[TX stop] ; data[TX stop]; 'buckets'[Eind tijd])
-
if('buckets'[Start tijd] < 'data'[TX start];'data'[TX start];'buckets'[Start tijd])
;0)*24*data[estimatedspeed])

I get this result:

	S	T

2000300
3000400
4000500
5000600
6000700
7000800
8000900
9001000
10001100
11001200
12001300
13001400
14001500
15001600
16001700
17001800
18001900
19002000
20002100
21002200
22002300
23002400
24002500
25002600

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]
[TD="bgcolor: #DCE6F1"]Rijlabels[/TD]
[TD="bgcolor: #DCE6F1"]Meting 1[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]39,00[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]13,23[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]18,98[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]29,76[/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]45,02[/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]12,00[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]5,00[/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]18,00[/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #DCE6F1"]Eindtotaal[/TD]
[TD="bgcolor: #DCE6F1, align: right"]181,00[/TD]

</tbody>

Blad3

The result is a perfectly round number which deviates (181/178)%
Also the numbers in the buckets don't really seem to correspond with reality.

b.downey · Sep 26, 2012

Are you all set? Or do you need some additional assistance?

bertusavius · Sep 26, 2012

I'm affraid I'm not entirely set.
As you compare the data with the result, you see the bins do not acurately represent the actual driven miles per hour. Perhaps the Average speed could be more acurately calculated.

I'll be back tomorrow. Thank you for your swift replies.

b.downey · Sep 26, 2012

Oh... I was not aware that you were looking for a miles per hour (speed) calculation. I was look at it as the number of total miles traveled in each bin (hour)... Clearly two entirely different calculation!

let me take another look at the post and solutions.

also, for the sample data you provide, could you provide the number you would like to see in each bin. This would be a tremendous help

b.downey · Sep 26, 2012

I am still not real clear on your requirments, but Here is another shoot at the issue... The Code below provides
-The Miles Traveled in each hour (Bin)
-The Time of actual travel in each hour (Bin)
-The KmPH (Average)

Code:

Option Explicit
Type typRec
    ID As Integer
    BinArray(24) As Double
    BinMins(24) As Long
End Type
Sub Calc()
    Dim Rec() As typRec
    ReDim Rec(0)
    Dim ws As Worksheet
    
    Dim RowNo As Long
    
    Dim StartTime As Date
    Dim EndTime As Date
    
    Dim IdIdx As Integer
    Dim BinIdx As Integer
    Dim HourCnt As Integer
    Dim Miles As Long
    
    Dim TotalMin As Long
    Dim Min As Integer
    
    Dim Perc As Single
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    RowNo = 2
    For RowNo = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        IdIdx = FindIdx(ws.Cells(RowNo, 1), Rec)
        StartTime = ws.Cells(RowNo, 2)
        EndTime = ws.Cells(RowNo, 3)
        Miles = ws.Cells(RowNo, 5) - ws.Cells(RowNo, 4)
        HourCnt = Hour(EndTime) - Hour(StartTime)
        
        Select Case HourCnt
            Case 0
                BinIdx = Hour(StartTime)
                TotalMin = DateDiff("n", StartTime, EndTime)
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + Miles
                Rec(IdIdx).BinMins(BinIdx) = Rec(IdIdx).BinMins(BinIdx) + TotalMin
            Case Is > 0
                TotalMin = DateDiff("n", StartTime, EndTime)
                '***** Determine fractional time for 1st hour
                BinIdx = Hour(StartTime)
                Min = DateDiff("n", StartTime, CDate(BinIdx + 1 & ":00:00"))
                Perc = Min / TotalMin
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
                Rec(IdIdx).BinMins(BinIdx) = Rec(IdIdx).BinMins(BinIdx) + Min
                
                Perc = 60 / TotalMin
                For BinIdx = Hour(StartTime) + 1 To Hour(EndTime) - 1
                    Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
                    Rec(IdIdx).BinMins(BinIdx) = Rec(IdIdx).BinMins(BinIdx)
                Next BinIdx
                
                '***** Determine fractional time for Last hour
                BinIdx = Hour(EndTime)
                Min = DateDiff("n", CDate(BinIdx & ":00:00"), EndTime)
                Perc = Min / TotalMin
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
                Rec(IdIdx).BinMins(BinIdx) = Rec(IdIdx).BinMins(BinIdx) + Min
            Case Else
    
        End Select
    Next RowNo
    
Call OutputResults(Rec)
End Sub
Function OutputResults(Rec() As typRec)
    Dim I As Integer
    Dim BinIdx As Integer
    Dim ws As Worksheet
    Dim RowNo As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ws.Cells.ClearContents
    
    ws.Cells(1, "A") = "ID"
    ws.Cells(1, "B") = "Bin (Hour)"
    ws.Cells(1, "C") = "Miles Traveled"
    ws.Cells(1, "D") = "Minutes"
    ws.Cells(1, "E") = "KmPH"
    
    RowNo = 2
    For I = 1 To UBound(Rec)
        For BinIdx = 1 To 24
            If Rec(I).BinArray(BinIdx) > 0 Then
                ws.Cells(RowNo, 1) = Rec(I).ID
                ws.Cells(RowNo, 2) = BinIdx & ":00 - " & (BinIdx + 1) & ":00"
                ws.Cells(RowNo, 3) = Format(Rec(I).BinArray(BinIdx), "0.00")
                ws.Cells(RowNo, 4) = Rec(I).BinMins(BinIdx)
                ws.Cells(RowNo, 5) = Format(Rec(I).BinArray(BinIdx) / Rec(I).BinMins(BinIdx) * 60, "0.00")
                RowNo = RowNo + 1
            End If
        Next BinIdx
    Next I
    
    ws.Rows("1:1").WrapText = True
    ws.Columns("C:C").Style = "Comma"
    ws.Columns("E:E").Style = "Comma"
End Function
Function FindIdx(ID As Integer, Rec() As typRec) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Rec)
        If Rec(I).ID = ID Then
            FindIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).ID = ID
    FindIdx = I
    
End Function

Based on the Data in your most recent post, the code creates the following

[TABLE="class: grid, width: 336, align: left"]
<TBODY>[TR]
[TD]ID</SPAN>[/TD]
[TD]Bin (Hour)</SPAN>[/TD]
[TD] Miles Traveled </SPAN>[/TD]
[TD]Minutes</SPAN>[/TD]
[TD] KmPH </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]15:00 - 16:00</SPAN>[/TD]
[TD] 39.00 </SPAN>[/TD]
[TD]74</SPAN>[/TD]
[TD] 31.62 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]16:00 - 17:00</SPAN>[/TD]
[TD] 13.23 </SPAN>[/TD]
[TD]41</SPAN>[/TD]
[TD] 19.36 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]17:00 - 18:00</SPAN>[/TD]
[TD] 18.98 </SPAN>[/TD]
[TD]55</SPAN>[/TD]
[TD] 20.71 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]18:00 - 19:00</SPAN>[/TD]
[TD] 29.76 </SPAN>[/TD]
[TD]78</SPAN>[/TD]
[TD] 22.89 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]19:00 - 20:00</SPAN>[/TD]
[TD] 45.02 </SPAN>[/TD]
[TD]91</SPAN>[/TD]
[TD] 29.69 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]20:00 - 21:00</SPAN>[/TD]
[TD] 12.00 </SPAN>[/TD]
[TD]23</SPAN>[/TD]
[TD] 31.30 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]21:00 - 22:00</SPAN>[/TD]
[TD] 5.00 </SPAN>[/TD]
[TD]13</SPAN>[/TD]
[TD] 23.08 </SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]22:00 - 23:00</SPAN>[/TD]
[TD] 18.00 </SPAN>[/TD]
[TD]24</SPAN>[/TD]
[TD] 45.00 </SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

b.downey · Oct 3, 2012

Code:

Option Explicit
Const DateCol As Integer = 1
Const StartTimeCol As Integer = 2
Const EndTimeCol As Integer = 3
Const CarNoCol As Integer = 5
Const StartMileCol As Integer = 6
Const EndMileCol As Integer = 7
Type typRawRec
    t As Date
    mile As Long
End Type
Type typCar
    CarNo As Integer
    rec() As typRawRec
End Type
Type typDT
    trvlDate As String
    Car() As typCar
End Type
    
Sub Process()
    Dim ws As Worksheet
    Dim arrDt() As typDT
    
    Dim RowNo As Long
    Dim LastRow As Long
    
    Dim StartTime As Date
    Dim Idx As Long
    Dim I As Long
    
    Dim DtIdx As Integer
    Dim CarIdx As Integer
    
    Set ws = ThisWorkbook.Worksheets(1)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ReDim arrDt(0)
    ReDim arrDt(0).Car(0)
    ReDim arrDt(0).Car(0).rec(0)
    
    For RowNo = 2 To LastRow
        If Len(Trim(ws.Cells(RowNo, StartTimeCol))) > 1 Then
            
            DtIdx = FindDtIdx(ws.Cells(RowNo, DateCol), arrDt)
            CarIdx = FindCarIdx(ws.Cells(RowNo, CarNoCol), arrDt(DtIdx).Car)
            Idx = UBound(arrDt(DtIdx).Car(CarIdx).rec)
            
            ReDim Preserve arrDt(DtIdx).Car(CarIdx).rec(Idx + 2)
            Idx = Idx + 1
            arrDt(DtIdx).Car(CarIdx).rec(Idx).t = CDate(ws.Cells(RowNo, StartTimeCol))
            arrDt(DtIdx).Car(CarIdx).rec(Idx).mile = Val(ws.Cells(RowNo, StartMileCol))
            Call InsertRec(arrDt(DtIdx).Car(CarIdx).rec, Idx)
            
            Idx = Idx + 1
            'rec(Idx).Id = Val(ws.Cells(RowNo, 1))
            arrDt(DtIdx).Car(CarIdx).rec(Idx).t = CDate(ws.Cells(RowNo, EndTimeCol))
            arrDt(DtIdx).Car(CarIdx).rec(Idx).mile = Val(ws.Cells(RowNo, EndMileCol))
            Call InsertRec(arrDt(DtIdx).Car(CarIdx).rec, Idx)
        End If
    Next RowNo
    
    Call OutputAll(arrDt)
    
    MsgBox "Complete", vbInformation
End Sub
Function OutputAll(arrDt() As typDT)
    Dim ws As Worksheet
    Dim RowNo  As Integer
    Dim DtIdx As Integer
    
    Dim CarIdx As Integer
    
    Set ws = ThisWorkbook.Worksheets(2)
    ws.Cells.ClearContents
    RowNo = 1
    
    ws.Cells(RowNo, 1) = "Date"
    ws.Cells(RowNo, 2) = "Car"
    ws.Cells(RowNo, 3) = "Bucket"
    ws.Cells(RowNo, 4) = "Kms"
            
    For DtIdx = 1 To UBound(arrDt)
        For CarIdx = 1 To UBound(arrDt(DtIdx).Car)
            Call OutputData(ws, arrDt(DtIdx).Car(CarIdx).rec, arrDt(DtIdx).trvlDate, arrDt(DtIdx).Car(CarIdx).CarNo)
        Next CarIdx
    Next DtIdx
End Function
Function OutputData(ws As Worksheet, rec() As typRawRec, Dt As String, CarNo As Integer)
    Dim I As Integer
    Dim RowNo As Long
    
    Dim StartTime As Date
    Dim StartMiles As Long
    
    Dim ElapsedMin   As Long
    Dim ElapsedMiles   As Long
    
    Dim Perc As Single
    Dim Min As Integer
    
    StartTime = rec(1).t
    StartMiles = rec(1).mile
    
    Dim Car(0) As typCar
    RowNo = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
    
    For I = 2 To UBound(rec)
        
        If Hour(StartTime) <> Hour(rec(I).t) Then
            ElapsedMin = DateDiff("n", rec(I - 1).t, rec(I).t)
            ElapsedMiles = rec(I).mile - rec(I - 1).mile
            
            '----- Need to Adjust Bins
            '***** Determine Offset Percentage
            Min = DateDiff("n", rec(I - 1).t, CDate(Hour(rec(I - 1).t) + 1 & ":00:00"))
            Perc = Min / ElapsedMin
            
            '***** Apply Offset
            rec(I - 1).mile = rec(I - 1).mile + ElapsedMiles * Perc
            rec(I).mile = rec(I).mile - ElapsedMiles * (1 - Perc)
            
            '***** Output Data
            ws.Cells(RowNo, 1) = Dt
            ws.Cells(RowNo, 2) = CarNo
            ws.Cells(RowNo, 3) = Hour(StartTime) & ":00" & " ~ " & (Hour(StartTime) + 1) & ":00"
            ws.Cells(RowNo, 4) = rec(I - 1).mile - StartMiles
            
            '***** Reset start time and miles
            StartTime = rec(I).t
            StartMiles = rec(I).mile
            RowNo = RowNo + 1
            I = I + 1
        End If
    Next I
    
    Debug.Print CarNo, UBound(rec)
    
    ws.Cells(RowNo, 1) = Dt
    ws.Cells(RowNo, 2) = CarNo
    ws.Cells(RowNo, 3) = Hour(StartTime) & ":00" & " ~ " & (Hour(StartTime) + 1) & ":00"
    ws.Cells(RowNo, 4) = rec(I - 1).mile - StartMiles
            
End Function
Function InsertRec(arrRec() As typRawRec, Idx As Long)
    Dim I As Integer
    Dim xRec As typRawRec
    
    '*****  This function sorts the last record into the correct location
    For I = Idx - 1 To 1 Step -1
        If arrRec(I).t > arrRec(I + 1).t Then
            xRec = arrRec(I + 1)
            arrRec(I + 1) = arrRec(I)
            arrRec(I) = xRec
        Else
            Exit Function
        End If
    Next I
End Function
Function FindCarIdx(ByVal CarNo As Integer, Car() As typCar) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Car)
        If CarNo = Car(I).CarNo Then
            FindCarIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Car(I)
    Car(I).CarNo = CarNo
    
    ReDim Car(I).rec(0)
    
    FindCarIdx = I
End Function
Function FindDtIdx(ByVal Dt As Date, arrDt() As typDT) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(arrDt)
        If Dt = arrDt(I).trvlDate Then
            FindDtIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve arrDt(I)
    arrDt(I).trvlDate = Dt
 
    ReDim arrDt(I).Car(0)
    ReDim arrDt(I).Car(0).rec(0)
    
    FindDtIdx = I
End Function

b.downey · Oct 4, 2012

This version of the code accounts for the new columns that you added. It will also be able to to the calculations seperating the data by Date, Driver, Car and Bin.

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 typHourRec
    Used As Boolean
    
    MinMin As Long
    MaxMin As Long
    
    MinMile As Long
    MaxMile As Long
End Type
Type typRec
    Key As String
    HrDetail(24) As typHourRec
End Type
Sub Process()
    Dim ws As Worksheet
    Dim Rec() As typRec
    Dim Key As String
    Dim KeyIdx As Integer
    
    Dim RowNo As Long
    Dim LastRow As Long
    
    Dim I As Long
    
    ReDim Rec(0)
    
    Set ws = ThisWorkbook.Worksheets(1)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For RowNo = 2 To LastRow
        Key = Trim(ws.Cells(RowNo, DateCol)) & "~" & Trim(ws.Cells(RowNo, DriverCol)) & "~" & Trim(ws.Cells(RowNo, CarNoCol))
        KeyIdx = FindKeyIdx(Key, Rec)
        Debug.Print KeyIdx
        If Len(Trim(ws.Cells(RowNo, StartTimeCol))) > 1 Then
            Call UpdateRec(Rec(KeyIdx).HrDetail, StartTimeCol, StartMileCol, ws, RowNo)
            Call UpdateRec(Rec(KeyIdx).HrDetail, EndTimeCol, EndMileCol, ws, RowNo)
        End If
    Next RowNo
    
    Call OutputAll(Rec)
End Sub
Function OutputAll(Rec() As typRec)
    Dim ws As Worksheet
    Dim RowNo  As Integer
    Dim KeyIdx As Integer
    
    Dim v As Variant
    Dim I As Integer
    
    Set ws = ThisWorkbook.Worksheets(2)
    ws.Cells.ClearContents
    RowNo = 1
    
    ws.Cells(RowNo, 1) = "Date"
    ws.Cells(RowNo, 2) = "Driver"
    ws.Cells(RowNo, 3) = "Car"
    ws.Cells(RowNo, 4) = "Bucket"
    ws.Cells(RowNo, 5) = "Kms"
    
    For KeyIdx = 1 To UBound(Rec)
        Call OutputData(ws, Rec(KeyIdx).HrDetail, Rec(KeyIdx).Key)
    Next KeyIdx
End Function
Function OutputData(ws As Worksheet, HrDetail() As typHourRec, Key As String)
    Dim I As Integer
    Dim RowNo As Long
    
    Dim v As Variant
    Dim J As Integer
    
    Dim ElapsedMin   As Long
    Dim ElapsedMiles   As Long
    
    Dim Perc As Single
    Dim tempMin As Integer
    Dim tempMiles As Long
    
    RowNo = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row + 1
    
    For I = 2 To UBound(HrDetail)
        
        If HrDetail(I).Used Then
            v = Split(Key, "~")
            If UBound(v) >= 0 Then
                For J = 0 To 2
                    ws.Cells(RowNo, J + 1) = v(J)
                    Debug.Print v(J)
                Next J
            End If
            ElapsedMiles = 0
            ElapsedMiles = HrDetail(I).MaxMile - HrDetail(I).MinMile
            
            '***** Determine Offset Adj for the Start of the Hour
            If HrDetail(I - 1).Used Then
                tempMin = (60 - HrDetail(I - 1).MaxMin) + HrDetail(I).MinMin
                tempMiles = HrDetail(I).MinMile - HrDetail(I - 1).MaxMile
                Perc = HrDetail(I).MinMin / tempMin
                ElapsedMiles = ElapsedMiles + (tempMiles * Perc)
            End If
            
            '***** Determine Offset Ady for the Top of the Hour
            If (I < 24) Then
                If HrDetail(I + 1).Used Then
                    tempMin = (60 - HrDetail(I).MaxMin) + HrDetail(I + 1).MinMin
                    tempMiles = HrDetail(I + 1).MinMile - HrDetail(I).MaxMile
                    Perc = (60 - HrDetail(I).MaxMin) / tempMin
                    ElapsedMiles = ElapsedMiles + (tempMiles * Perc)
                End If
            End If
            
            'ws.Cells(RowNo, 1) = Dt
            'ws.Cells(RowNo, 2) = CarNo
            ws.Cells(RowNo, 4) = I & ":00" & " ~ " & (I + 1) & ":00"
            ws.Cells(RowNo, 5) = ElapsedMiles
            RowNo = RowNo + 1
        End If
        
    Next I
            
End Function
Function UpdateRec(HrDetail() As typHourRec, ByVal TimeColNo As Long, ByVal MileColNo As Long, ws As Worksheet, RowNo As Long)
    Dim TempTime As Date
    Dim Idx As Long
    
    TempTime = CDate(ws.Cells(RowNo, TimeColNo))
    Idx = Hour(TempTime)
    
    Select Case HrDetail(Idx).Used
        Case False
            HrDetail(Idx).MaxMin = Minute(ws.Cells(RowNo, TimeColNo))
            HrDetail(Idx).MinMin = Minute(ws.Cells(RowNo, TimeColNo))
            HrDetail(Idx).MaxMile = Val(ws.Cells(RowNo, MileColNo))
            HrDetail(Idx).MinMile = Val(ws.Cells(RowNo, MileColNo))
            HrDetail(Idx).Used = True
        Case True
            HrDetail(Idx).MaxMile = UpdateMax(HrDetail(Idx).MaxMile, Val(ws.Cells(RowNo, MileColNo)))
            HrDetail(Idx).MaxMin = UpdateMax(HrDetail(Idx).MaxMin, Minute(ws.Cells(RowNo, TimeColNo)))
            HrDetail(Idx).MinMile = UpdateMin(HrDetail(Idx).MinMile, Val(ws.Cells(RowNo, MileColNo)))
            HrDetail(Idx).MinMin = UpdateMin(HrDetail(Idx).MinMin, Minute(ws.Cells(RowNo, TimeColNo)))
    End Select
End Function
Function FindKeyIdx(ByVal Key As String, Rec() As typRec) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Rec)
        If Key = Rec(I).Key Then
            FindKeyIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).Key = Key
    
    'ReDim Rec(I).HrDetail(0)
    
    FindKeyIdx = I
End Function
Function UpdateMin(arrVal As Long, wsVal As Long) As Long
    If wsVal < arrVal Then
        UpdateMin = wsVal
    Else
        UpdateMin = arrVal
    End If
End Function
Function UpdateMax(arrVal As Long, wsVal As Long)
    If wsVal > arrVal Then
        UpdateMax = wsVal
    Else
        UpdateMax = arrVal
    End If
End Function

bertusavius · Oct 5, 2012

Fantastic!
It works really well

Just having a very minor issue with the dates:
The database uses d/m/y layout for a date, but your macro transforms the dates into m/d/y. (which naturally causes an error as soon as the day exceeds 12)

complicated way of dividing data

bertusavius

Board Regular

Laurent C

Board Regular

Excel Facts

b.downey

Active Member

bertusavius

Board Regular

b.downey

Active Member

bertusavius

Board Regular

b.downey

Active Member

b.downey

Active Member

b.downey

Active Member

b.downey

Active Member

bertusavius

Board Regular

Similar threads

Forum statistics

Share this page

complicated way of dividing data

Board Regular

Board Regular

Excel Facts

Active Member

Board Regular

Active Member

Board Regular

Active Member

Active Member

Active Member

Active Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock