Day & Night Time calculations with breaks and seperate columns for Reg/OT.

[toni689]

I have a time sheet that was working great, until I needed to figure night shift hours.

I have columns for clock in/out and lunch in/out. I also need columns to show the number of regular (normal pay) hours and the number of OT hours.

C = clock in, D = clock out lunch, E = clock in lunch, F = clock out.

If statements were used to designate 8 hours for the Regular column:
=IF(((D12-C12)+(F12-E12))*24>8,8,((D12-C12)+(F12-E12))*24)

And also for the OT column:
=IF(((D12-C12)+(F12-E12))*24>8,((D12-C12)+(F12-E12))*24-8,0)

These work great for the way the sheet is setup, but do not play nicely with night shift hours. I need to have a formula that works either way so that I can just plug in the times. I have searched around a bit and tried a few things, but none seem to work in both cases. I'm sure there's a nested +1 or something way to do this, but it eludes me.

Anyone have ideas?

 

[AD_Taylor]

Welcome to the forum!

Can you post an example of the 4 columns (C:F) for a night shift?

Can completely understand what you're doing for standard hours but need to know how lunch might fall if it was a night shift.

 

[toni689]

[TABLE="width: 168"]
<tbody>[TR]
[TD="class: xl68, width: 225, bgcolor: transparent, colspan: 4"]Time
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]In
[/TD]
[TD="class: xl63, bgcolor: transparent"]Out
[/TD]
[TD="class: xl63, bgcolor: transparent"]In
[/TD]
[TD="class: xl64, bgcolor: transparent"]Out
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7:15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]18:57
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]19:40
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6:45
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16:04
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]18:15
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7:12
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20:45
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0:00
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0:30
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8:22

[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the quick reply! This is one of the weeks that I am looking at. This is why I want one formula for either situation. I do not want to have to edit or copy over seperate formulas according to what day they work nights vs days.

Below is the break down that we use. I need an hourly total for each item listed, but want the Straight (regular) time to only display up to 40 and the added to be placed into OT unless it falls under Sick or Vacation. The lunch column here is not needed, but I added it to try to keep the other columns in check for totals.

[TABLE="width: 287"]
<tbody>[TR]
[TD="colspan: 5"]Hourly Breakdown
[/TD]
[/TR]
[TR]
[TD]Straight
[/TD]
[TD]Overtime
[/TD]
[TD]Lunch
[/TD]
[TD]Sick
[/TD]
[TD]Vacation
[/TD]
[/TR]
[TR]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8.00
[/TD]
[TD][/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]8.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]12.95
[/TD]
[TD="align: right"]4.95
[/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]11.62
[/TD]
[TD="align: right"]3.62
[/TD]
[TD="align: right"]0.5
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD="align: right"]40.00
[/TD]
[TD="align: right"]8.57
[/TD]
[TD="align: right"]2.50
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00

[/TD]
[/TR]
</tbody>[/TABLE]

 

[AD_Taylor]

Something like this looks like it works

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Time					Hourly Breakdown
2	In	Out	In	Out	Hours Worked	Straight	Overtime	Lunch	Sick	Vacation
3
4	07:15	11:00	11:30	18:57	11.20	8.00	3.20	0.50	0	0
5	07:00	11:00	11:30	19:40	12.17	8.00	4.17	0.50	0	0
6	06:45	11:00	11:30	16:04	8.82	8.00	0.82	0.50	0	0
7	18:15	00:00	00:30	07:12	12.45	8.00	4.45	0.50	0	0
8	20:45	00:00	00:30	08:22	11.12	8.00	3.12	0.50	0	0
9	20:45	23:59	00:29	08:22	11.12	8.00	3.12	0.50	0	0
10	18:15	23:00	23:30	07:12	12.45	8.00	4.45	0.50	0	0
11	20:45	01:00	01:30	08:22	11.12	8.00	3.12	0.50	0	0
Sheet1

Cell Formulas
Range	Formula
E4	=((IF(B4 > A4, B4, B4 + 1) - A4) + (IF(D4 > C4, D4, D4 + 1) - C4)) * 24
F4	=IF(E4 > 8, 8, E4)
G4	=E4 - F4
H4	=(IF(C4 > B4, C4, C4 + 1) - B4) * 24

I've added a couple of my own examples at the bottom to check for other possibilities and it all seems to work.

 

[toni689]

That looks good! I am going to try it. I think maybe I was trying to over formulate it a bit!

 

[toni689]

This works great except for days where there are no values (employee did not work). I just added another IF in the front checking for 0 in the clock in slot.
Thanks for the help!!

 

[AD_Taylor]

Great glad it works for you and thanks for the feedback