# Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

1. ## Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

Hi!

I'm working with an excel spread sheet to calculate the total hours schedule for the week within different departments of our housekeeping office. I need it to total the hours scheduled for each day and then total for the entire week. I'm running into a problem when trying to subtract a half hour lunch from a day, if the shift is 6 or more hours long.
I've been working on this for a few days now, and haven't been able to come up with a solution. And I've searched on this forum for questions similar to mine, but none of the solutions worked in my spread sheet.

Column D has the same formula. Column E has several different formulas I've tried, but none of them seem to work.
excel help.xls

2. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

hi check this out

http://www.mrexcel.com/forum/excel-q...-question.html

3. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

H and welcome to the forum

Keep in mind that excel treats time as a fraction/decimal of 1 (day). So 12:00 mid day is actually 0.5, and 06:00am is 0.25.

30 minutes is either 0.0208333333333333 or 30/60/24

plug that into your formula and see how far you get

Another observation - if you are just adding (or subtracting) 2 numbers, you dont need to use the SUM() function.
=IF(C7>0,SUM(C7-B7),"0")
can be shortened to...
=IF(C7>0,C7-B7,0)

4. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

I used the formula =IF(C1<=TIME(6,0,0),C1,C1-TIME(0,30,0)) from the forum you linked me to, and it works great for subtracting the half houre. Now the only problem I have is that when someone is scheduled off, I have the formula calculating "0" in column D, but it's creating a problem in column F. It just shows "#######" in column F and it messes with the totaled hours for the day. So the total for Sunday is showing as 14:30 when it should be 16.

excel help2.xls

5. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

Try this in column E : =IF("6">D7,D7-"0.0205",D7)

6. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

I tried it, but it still gave me no value with the rows that are "0"

7. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

Ok. The trick I think is then to add a column for the days when people are off (maybe a tick box?), and then change the format from Time, to Number with 2 decimal places (ie 08:00 becomes 08.00). This allows you to calculate easily using 0.5 for a half hour, and gives you a lot of options for further use. Would this be any good? I'll knock you up a working copy if you like. Let me know.

8. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

That would be fantastic if you could!!

9. ## Re: Employee Schedule - Subtracting a 30 min lunch from a schedule 6 or more hours long

Hi.

Try this for size. I have left some formatting undone in-case you want to change it, although if you do use it then i'd suggest protecting the cells that have formulas in.

I have changed the layout, and added some stuff you may not like, but worst case scenario it may give you some ideas.

Regards,

Digger

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1