Excel VBA. Calculating worktime with different overtimes

[janbanan]

Hello.

I have a Form where the user enters Start-time and Stop-time.
The form has four fields, T1 for start hour (0-24) and M1 for minutes,
T2 and M2 for end of workday. Ex: 09 45 and 18 00.

I have to calculate two different overtime values.
First is overtime between 15:00 to 21:00
Second is overtime from 21:00 to 07:00 (next morning)

Normal worktime, whitout overtime is 07:00 to 15:00

I want the resulting overtime to be in the format h,m (3,30 for 3 hours and 30 minutes)

Can anyone here show me a nice solution for this?


Many thanks in advance, Janbanan

[Bob Phillips]

You need to show us some examples, worked through.

[janbanan]

1.
User enter 14 00 as start of work and 22 45 as end.
This would give an overtime calculation of 6 hours for time between 15:00 and 21:00 (21-15) and 1 hour 45 minutes for overtime between 21:00 and 07:00 (22,45 - 21)

2.
User enter 05 50 as start of work and 16 00 as end.
This would give an overtime calculation of 1 hour for overtime between 15:00 and 21:00 (16-15) and 1 hour and 10 minutes for time between 21:00 and 07:00
(7-5,50)


3.
User enter 23 30 as start of work and 04 00 as end.
This would give an overtime calculation of 4 hours and 30 minutes
(24-23,30+4) for overtime between 21:00 and 07:00

Hope this makes things clearer.

Tnx

[Bob Phillips]

=MOD(MAX(0,--(T2&":"&M2)-"21:00"),1)+MOD(MAX(0,"07:00"-(T1&":"&M1)),1)

and

=MOD(MIN(U2,--"21:00")-MAX(--(T1&":"&M1),--"15:00"),1)

[janbanan]

Tnx for your answer.

But I don't understand it.

X MOD 1 is allways zero...
Could you show me in more 'VBA-style'?

[Bob Phillips]

X Mod 1 is not always 0. 2.1 Mod 1 is .1