Need to know how to remove 30 minutes if number hits 8 hours - For a work schedule

[avrilb546]

Hi there. I'm absolutely no expert on excel. But, I'm a new manager and need to create a schedule for the team to use. I want to include the 30 minute break to make calculations easier for my boss. We work on a two week schedule (Mon-Sun) and I don't want to have 2 sections blocked off for breaks. I want to just create a part of the formula that automatically deducts a half hour break if someones hours reach or exceed 8 hours. Here is what two days looks like, and the string of data I have now.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A3[/TD]
[TD]B3[/TD]
[TD]D3[/TD]
[TD]E3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]10:00[/TD]
[TD]13:00[/TD]
[TD]10:00[/TD]
[TD]18:00[/TD]
[TD]11.00[/TD]
[/TR]
</tbody>[/TABLE]

So, because there is an 8 hour shift, I want the total hours to say 10.30 not 11.00

Here is the data I have for these two days. (I have it filled out for all 7 days in each week, but I cut it down to make it easier) (Data was too long so I had to put image sorry)
<a3),((b3-a3)*24)+24,(b3-a3)*24)),2)+round(if((or(d3="",e3="")),0,if((e3<d3),((e3-d3)*24)+24,(e3-d3)*24)),2)
<a3),((b3-a3)*24)+24,(b3-a3)*24)),2)+round(if((or(d3="",e3="")),0,if((e3<d3),((e3-d3)*24)+24,(e3-d3)*24)),2)
<a3),((b3-a3)*24)+24,(b3-a3)*24)),2)
<a3),((b3-a3)*24)+24,(b3-a3)*24)),2)

<a3),((b3-a3)*24)+24,(b3-a3)*24)),2)
I hope that's not to much to ask for. I've reached a dead end. I've looked at tons of different options but they all want me to include a "Time in, Time out" column. But I really don't want that.</a3),((b3-a3)*24)+24,(b3-a3)*24)),2)></a3),((b3-a3)*24)+24,(b3-a3)*24)),2)
</a3),((b3-a3)*24)+24,(b3-a3)*24)),2)
</a3),((b3-a3)*24)+24,(b3-a3)*24)),2)+round(if((or(d3="",e3="")),0,if((e3<d3),((e3-d3)*24)+24,(e3-d3)*24)),2)
</a3),((b3-a3)*24)+24,(b3-a3)*24)),2)+round(if((or(d3="",e3="")),0,if((e3<d3),((e3-d3)*24)+24,(e3-d3)*24)),2)

 

[kweaver]

If you don't put in the time in and time out, does that mean you'll enter the total time for a day (e.g., Monday = 3; Tuesday = 8; etc.)?

If you have 7 days from A:G columns, then H might look like this for total hours:

=SUM(A4:G4)-0.5*COUNTIF(A4:G4,">=8")

Using your example, this would show as 10.5 hours.

 

[avrilb546]

No, if there is no time in or out data like if Wednesday came and there were no hours it would just remain empty. This is my actual table. For example, I need the first line to say 30 hours, not 32 because the formula should deduct the 30 minute breaks. But the second line with 6 hours doesn't need the deduction because it's only a 3 hour shift.

 

[kweaver]

No image is posted or showing so it's anyone's guess where that 30 or 32 hours is.

 

[kweaver]

Could you live with something like this?