Time Range Split

[termsig]

Hello,

I'm trying get my head around how i can get to the below done using function instead doing manually.
Basically I need split the units value based on the minutes allocated within time slot (start time & end time)

ie, for the first line because it's between 04:00 and 05:00 it will allocate all 100 units after 04:00.
Where for 2nd example this is split over two time slots. It will be used for all 24h period and for around 10,000 row's.

[TABLE="width: 698"]
<tbody>[TR]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Minutes[/TD]
[TD="align: center"]Units[/TD]
[TD="align: center"]03:00[/TD]
[TD="align: center"]04:00[/TD]
[TD="align: center"]05:00[/TD]
[TD="align: center"]06:00[/TD]
[TD="align: center"]07:00[/TD]
[TD="align: center"]08:00[/TD]
[TD="align: center"]09:00[/TD]
[/TR]
[TR]
[TD="align: center"]4:05[/TD]
[TD="align: center"]4:55[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4:34[/TD]
[TD="align: center"]5:35[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] 43[/TD]
[TD="align: center"] 57[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6:05[/TD]
[TD="align: center"]7:58[/TD]
[TD="align: center"]113[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] 49[/TD]
[TD="align: center"] 51[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Josh

 

[JustynaMK]

Hi Josh,

This is quite a long formula but cannot think of any shorter solution right now.

Please paste the following into cell E2 (right beneath 03:00) and drag it down & accross:

=IF(HOUR($A2)=HOUR($B2),IF(HOUR($A2)=HOUR(E$1),100,0),IF(HOUR($A2)=HOUR(E$1),(MINUTE(TIME(HOUR($B2),0,0)-$A2))*100/(MINUTE(TIME(HOUR($B2),0,0)-$A2)+MINUTE($B2)),IF(HOUR($B2)=HOUR(E$1),MINUTE($B2)*100/(MINUTE(TIME(HOUR($B2),0,0)-$A2)+MINUTE($B2)),0)))