Hello,

I am making a work shift schedule for my company that is pretty basic. It has the employee names and then under the appropriate day it shows what shift they are working(ie. Monday 8-5). The problem I have is I would like to have excel somehow know that when I put "8-5" in a cell under a day that it means 8 hours paid(8-5, 9 hours minus 1 hour lunch). I have it so each employee has a "total hours" worked cell that adds up the hours each employee has worked for the week, but obviously it can't add up "8-5" because it doesn't know what that means. Is there any way to do what I want?

Thanks,
Matt

Of course, the easiest way is to use "hours since midnight" (24-hour time). Enter 8-17.

Best, however, is to have separate cells for start time and quit time, and put true dates and times there. This allows you to do date-time math, and covers shifts which cross midnight (graveyard shift, for instance).

Hi,

Ok, I figured out how to put time in using "time in", "time out" columns and have it so it will total hours for the week and minus 1 hour for unpaid break for each day[ex.(b1-a1-1/24)+(d1-c1-1/24)+...). The problem I have now is if I don't enter a time in, time out for a particular day(say Tuesday), it still takes off one hour for an unpaid lunch for Tuesday. I need every day to minus 1 hour as each employee works all different days. I know there is conditional formatting but I don't know how to use it.

If anyone has an idea on how to fix this problem I would be greatful.

Thanks,
Matt