Formula to Calc Hrs Worked

[jschlapi]

Hello,
I wonder if I could once again ask for advice. I'm looking for a formula to calculate hours worked per shift based on IN / OUT time. My REGULAR SHIFT runs from 7:00 a.m. to 3:30 p.m. - all other hours are grouped into the OVERTIME column.

EXAMPLES:
A B C D E F
Time In Time Out Reg hrs OT Hrs Reg $ OT $
7:00 AM 11:00 AM 3 0 $30.00 $0.00
12:00 PM 6:30 PM 3.5 3 $35.00 $45.00
11:00 PM 3:15 AM 0 4.25 $0.00 $63.75

thank you for your help.

 

[Peter_SSs]

Not sure if you realised you posted this in the 'Test Here' forum? If it is a real question rather than a test post, reply here and somebody will move the thread to the 'Excel Questions' forum.

 

[jschlapi]

Sorry - no - I didn't realize I was still in the test area. Thank you for moving it.

joyce

 

[Peter_SSs]

Done

 

[T. Valko]

Hello,
I wonder if I could once again ask for advice. I'm looking for a formula to calculate hours worked per shift based on IN / OUT time. My REGULAR SHIFT runs from 7:00 a.m. to 3:30 p.m. - all other hours are grouped into the OVERTIME column.

EXAMPLES:
A B C D E F
Time In Time Out Reg hrs OT Hrs Reg $ OT $
7:00 AM 11:00 AM 3 0 $30.00 $0.00
12:00 PM 6:30 PM 3.5 3 $35.00 $45.00
11:00 PM 3:15 AM 0 4.25 $0.00 $63.75

thank you for your help.

Try this...

Not extensively tested but for what I did test it seems to work.

Book1
	A	B	C	D
1	In	Out	Reg	OT
2	7:00 AM	11:00 AM	4:00	0:00
3	7:15 AM	7:30 AM	0:15	0:00
4	11:00 PM	11:00 AM	4:00	8:00
Sheet1

Enter this formula in C2:

=(A2>B2)*MEDIAN(0,B2-7/24,8.5/24)+MAX(0,MIN(15.5/24,B2+(A2>B2))-MAX(7/24,A2))

Format as h:mm

Enter this formula in D2:

=MOD(B2-A2,1)-C2

Format as h:mm

Select C2:D2 and copy down as needed.

 

[jschlapi]

This is perfect, except it will not allow me to multiply the hours worked by rate of pay. Example: I would like: 4 hrs * $10 = $40.

any ideas?

Thank you,

 

[T. Valko]

This is perfect, except it will not allow me to multiply the hours worked by rate of pay. Example: I would like: 4 hrs * $10 = $40.

any ideas?

Thank you,

It would be:

Hours * 24 * Rate

=C2*24*10

Or, if you put the rate in a cell:

C2 = 4:00
E1 = 10

=C2*24*E1

Format as General or Number

 

[jschlapi]

This is perfect.....thank you very much for your help.http://www.mrexcel.com/forum/images/smilies/icon_smile.gif
joyce

 

[T. Valko]

This is perfect.....thank you very much for your help.http://www.mrexcel.com/forum/images/smilies/icon_smile.gif
joyce

You're welcome. Thanks for the feedback!

 

[jschlapi]

I'm Back......I wondered if there is a way to accommodate weekends. Weekends (day 7 and day 1) hours should go directly into the Overtime Hours column.

I think it should be some sort of IF statement, but not sure.....

Thank you.