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

hi check this out

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

Thanks for the advice!

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

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

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

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.

That would be fantastic if you could!!

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