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

#### avrilb546

##### New Member
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.

 A3 B3 D3 E3 Monday Monday Tuesday Tuesday Time In Time Out Time In Time Out Total Hours 10:00 13:00 10:00 18:00 11.00

<tbody>
</tbody>

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)

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### kweaver

##### Well-known Member
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:

Code:
``=SUM(A4:G4)-0.5*COUNTIF(A4:G4,">=8")``
Using your example, this would show as 10.5 hours.

Last edited:

#### avrilb546

##### New Member
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

##### Well-known Member
No image is posted or showing so it's anyone's guess where that 30 or 32 hours is.

#### kweaver

##### Well-known Member
Could you live with something like this? 