help: dividing time into "sub-time"

[Fedde]

I'm busy working on a schedule for calculating irregular hours.
A thing I need to do is the following:

name: Ed
From: 6:30
Till: 15:00
+15% :

Ed is one of the employees working on a busline. If he works between 6:00 and 7:30 he gets +15% on his salary for his worked hours. So what I need to know is how many hours(or minutes) he's worked in that 15% box. (In this example 1 hour)

I really can't sort this out, I hope someone could help me

[VBA Noob]

Hi,

Does this help

=IF(AND(A1<="06:00",A2<"07:30"),"07:30"-A1,"")

VBA Noob

[Dav]

Assuming your times are times and Start is in a1, and finish in a2 and no shift goes over midnight. Perhaps formated as time

=IF(AND(A1<0.25,A2>0.25),MIN(A2-0.25,0.0625),IF(A1>0.3125,0,MIN(0.3125-A1,A2-A1)))

or multiply the result by 24 for get decimal hours

Regards

Dav

[Fedde]

I'm going to try these ones, I'll keep you informed!!
Thanks!!!

[Fedde]

Still not working fine...

the formula's somewhere have an error...?

=IF(AND(A1<0.25,A2>0.25),MIN(A2-0.25,0.0625),IF(A1>0.3125,0,MIN(0.3125-A1,A2-A1)))

at the red arced piece it gives an error

[Dav]

Actually the formula should be

=IF(AND(A1<0.25,A2>0.25),MIN(A2-0.25,0.0625),IF(OR(A1>0.3125,A2<=0.25),0,MIN(0.3125-A1,A2-A1)))

I take you back to the intial statement if the times are times. If you apply a number format to the cells containing the times do they change? If they dont they are text and so will not evaluate. If so try

=IF(AND(TIMEVALUE(A1)<0.25,TIMEVALUE(A2)>0.25),MIN(TIMEVALUE(A2)-0.25,0.0625),IF(OR(TIMEVALUE(A1)>0.3125,TIMEVALUE(A2)<=0.25),0,MIN(0.3125-TIMEVALUE(A1),TIMEVALUE(A2)-TIMEVALUE(A1))))

Or post a zipped excel file to this thread so we can see what the problem is

Regards

Dav

[arthurbr]

This may help you out :
http://cpearson.com/excel/overtime.htm

[daddylonglegs]

Still not working fine...

the formula's somewhere have an error...?

=IF(AND(A1<0.25,A2>0.25),MIN(A2-0.25,0.0625),IF(A1>0.3125,0,MIN(0.3125-A1,A2-A1)))

at the red arced piece it gives an error

Some versions of excel need ; instead of ,

Assuming you have start time in A1 and end time in B1 and both A1 and B1 will always be on the same day then this formula will total hours between 06:00 and 07:30

=IF(OR(B1<"06:00"+0,A1>"07:30"+0),0,MIN(B1,"07:30"+0)-MAX(A1,"06:00"+0))

If you might have shifts that start one day and end the next, e.g. 22:00 - 07:00 then use this formula

=($A1>$B1)*MEDIAN(0,$B1-"06:00","07:30"-"06:00")+MAX(0,MIN("07:30"+0,$B1+($A1>$B1))-MAX("06:00"+0,$A1))

If either of these formulas give you errors you may need to replace the ,s with ;

[Fedde]

wow thanks i'm going to try it all out now. really appreciate it!

[Fedde]

=IF(OR(B1<"06:00"+0,A1>"07:30"+0),0,MIN(B1,"07:30" +0)-MAX(A1,"06:00"+0))

This one did it!

thanks everyone for helping me out!

[Carim]

Hi,

With Start Time in A1 and End Time in B1, use following formula :

Please Login or Register  to view this content.

and format cell hh:mm