How to create a formula for 35 minute breaks...

**tricialynn75** · 02-21-2009, 10:25 PM

Hello, I am new to this whole Excel thing. I have been receiving help from a friend and my Brother In Law. However, they have been busy and are unsure of how to help me in this matter.

First of all, I need to mention that I no longer have Excel at my home computer, I am using Open Office (which I am not used to yet.) I have Excel at work which is where I need the help.

I am trying to make an Employee Schedule that has hours worked for each individual Employee as well as Total Hours worked for each Day. My friend worked out a formula for that, however, some other Managers I work with are being difficult and now want to know if it's possible to minus out their 35 minute breaks. Which would need to be minused out for each Employee as well as each individual day.

For each shift that is scheduled more than 5 hours they get one 35 minute break. So if someone is scheduled for 5 hours exactly or less they would not receive a break. If someone is scheduled for a double shift they would receive two 35 minute breaks.

I hope this isn't too much. Thanks for any help you can give me.

I will attach the schedule I have so far.

Sincerely,
Tricia

**Ron Coderre** · 02-21-2009, 11:11 PM

I think I understand what you're trying to achieve.

I attached an edited version of the file you posted.
The changes I made are in Row_7 (see the highlighted cells):
Daily Hours Calc - New Formula:

D7: =MOD(C7-B7,1)-SUM(FREQUENCY(ROUND(MOD(C7-B7,1)*24,2),
{5,10,15})*{0;35;70;105})/60/24

Copy that formula into G7, J7, M7, P7, S7, V7

Total Hours Calculation - New Formula:

W7: =SUM(D7,G7,J7,M7,P7,S7,V7)

Does that help?

**JBeaucaire** · 02-21-2009, 11:24 PM

Here you go. You didn't indicate what triggered the second 35 minutes break deduction, but we can add that in next.

**JBeaucaire** · 02-21-2009, 11:46 PM

Ron's is way cooler. I'd suggest you use that.

**tricialynn75** · 02-22-2009, 12:07 AM

Wow! Thank you both so much for your help! I used Ron's schedule. I love the little plus and minus boxes to hide and unhide the hidden cells! I think this will work out wonderfully!

I did have one other quick question. Is it possible to add some sort of clear button that will just delete the cells that have/will have the scheduled times in them for when a new week comes around?

The scheduling manager likes to start with a clean slate of employees shifts and work from there.

Also, will this affect if we need to add any new or returning staff we will put back on the schedule?

I would like to offer up something for your help. I work at a movie theatre and would be willing to send you some movie tickets or perhaps there is a poster you would like?

Thanks again for the help!
Tricia

**Ron Coderre** · 02-22-2009, 12:25 AM

Honestly, no gift is necessary. We help because we enjoy sharing what we know. But, thanks so much for offering.

Regarding resetting the sheet....I usually find it's easiest to keep
a pristine template and distribute a copy. It's easier to
add or delete names and re-distribute than it is to trust any
worksheet that somebody else monkeyed around with.

Does that help?

**tricialynn75** · 02-22-2009, 12:29 AM

Yes, Ron, that does help. I so appreciate everything! You have made things so much easier for me. It's one less headache to worry about.

Thanks again!