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

avrilb546

New Member
Joined
Jun 9, 2019
Messages
2
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.

A3B3D3E3
MondayMondayTuesdayTuesday
Time InTime OutTime InTime OutTotal Hours
10:0013:0010:0018:0011.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)
 

Some videos you may like

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
Joined
May 8, 2018
Messages
1,040
Office Version
365, 2010
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
Joined
Jun 9, 2019
Messages
2
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
Joined
May 8, 2018
Messages
1,040
Office Version
365, 2010
No image is posted or showing so it's anyone's guess where that 30 or 32 hours is.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,040
Office Version
365, 2010
Could you live with something like this?

 

Watch MrExcel Video

Forum statistics

Threads
1,099,520
Messages
5,469,119
Members
406,637
Latest member
Mbsmbs

This Week's Hot Topics

Top