Employee Schedule - with lunch and off days

[swtsev]

Hello everyone,

I am working on creating an employee schedule with multiple employes. I have entered a start time and an end time for each shift. There are days where employess will be off and if working they will receive a half hour lunch. I have looked online and was able to find a formula =Mod(end time- start time,1)-1/48 which seems to work on the days worked but I am not sure how to get zero hours for the off days. I want the off days to read as off. I am also having issues summing up the hours for the week. I would greatly appreciate any help in this matter.

Thanks,
Michelle

Sunday Monday Tuesday
off 10:00 AM 9:00 AM
2:00 PM 5:00 PM

 

[Chameleon64]

You can use the following formula to calculate the time difference inc. lunch break, and show "off" if the employee isn't working (I've assumed your table starts in A1):

=IF(A2="off","off",(A3-A2)*24-0.5)

You can do a simple SUM(A4:G4) to calculate the total hours per week; the cells with "off" in will be treated as zeros.