Count spesific time within two time range

[iraqiboy90]

Hi

I'm having hard time figuring out how to auto calculate the following in excel

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Total[/TD]
[TD]12%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]23:00[/TD]
[TD]07:00[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]15:00[/TD]
[TD]23:00[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

So this is a work timetable where we get 12% extra if it's between 06:00 and 24:00
We get 50% extra for the time between 24:00 and 06:00

Now, I got the total formula figured out using =(07-23+(07<23))*24
but not the 12% or the 50%
Idk how to auto calculate the time between 24:00-06:00 and 06:00-24:00


Regards
iraqiboy90

 

[steve the fish]

Pasting your table into A1:E3 place in C2:

=(B2-A2+(A2>B2))*24

In D2:

=IF(A2>B2,"24:00"-A2+MAX(B2,"6:00")-"6:00",B2-MAX(A2,"6:00"))*24

In E2:

=C2-D2

Copy down.

 

[lrobbo314]

For 12%

=SUMPRODUCT(--((MOD((A2+(ROW(INDIRECT("1:"&C2))-1)/24),1)>=(6/24)))*((MOD((A2+(ROW(INDIRECT("1:"&C2))-1)/24),1)<(23.999/24))))

For 50 %

=SUMPRODUCT(--((MOD((A2+(ROW(INDIRECT("1:"&C2))-1)/24),1)>=0))*((MOD((A2+(ROW(INDIRECT("1:"&C2))-1)/24),1)<(6/24))))

 

[iraqiboy90]

thanks steve, it works