Waiting2Excel
New Member
- Joined
- Nov 22, 2017
- Messages
- 4
Hi,
I've been reading through threads for days and need help with a couple of formulas. I'm creating a scheduling spreadsheet for my office. I want it to calculate total hours scheduled for the office per day, and also total the employees total hours scheduled for the week, minus a 30 minute lunch break on days worked, and counting any "OFF" days or "Vacation" Days that I schedule as 0 so I do not receive any "#value s!".
<tbody>
</tbody>
So far I have been able to get it to calculate my scheduled hours for the week using this formula that I found online:
=(SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24
But I need it to subtract a 30 minute lunch ONLY from the days actually scheduled, and ignore the "OFF and/or VACATION" days.
I was able to calculate my total hours scheduled in the office for the day using this formula that I pieced together:
=(IF(COUNT(B4,C4)=2,MOD(C4-B4,1),0)-1/48)+(IF(COUNT(B5,C5)=2,MOD(C5-B5,1),0)-1/48)+(IF(COUNT(B6,C6)=2,MOD(C6-B6,1),0)-1/48)+(IF(COUNT(B7,C7)=2,MOD(C7-B7,1),0)-1/48)+(IF(COUNT(B8,C8)=2,MOD(C8-B8,1),0)-1/48)+(IF(COUNT(B9,C9)=2,MOD(C9-B9,1),0)-1/48)+(IF(COUNT(B10,C10)=2,MOD(C10-B10,1),0)-1/48)+(IF(COUNT(B11,C11)=2,MOD(C11-B11,1),0)-1/48)
But my issue is that it subtracts a 30 minute lunch from even the "OFF" days, and I get #value ! if I have blanks.
Please help, I'm so close yet sooo far. Thanks for any help
I've been reading through threads for days and need help with a couple of formulas. I'm creating a scheduling spreadsheet for my office. I want it to calculate total hours scheduled for the office per day, and also total the employees total hours scheduled for the week, minus a 30 minute lunch break on days worked, and counting any "OFF" days or "Vacation" Days that I schedule as 0 so I do not receive any "#value s!".
5-Jan | 6-Jan | 7-Jan | 8-Jan | 9-Jan | 10-Jan | 11-Jan | |||||||||
FRIDAY | SATURDAY | SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | |||||||||
START | STOP | START | STOP | START | STOP | START | STOP | START | STOP | START | STOP | START | STOP | TOTAL | |
1 | 12:00 PM | 10:00 PM | OFF | OFF | 10 | ||||||||||
2 | OFF | OFF | OFF | OFF | OFF | OFF | 1:00 PM | 5:00 PM | 5:00 PM | 10:15 PM | 9.25 | ||||
3 | 0 | ||||||||||||||
4 | 0 | ||||||||||||||
5 | 0 | ||||||||||||||
6 | 0 | ||||||||||||||
7 | 0 | ||||||||||||||
8 | 0 | ||||||||||||||
HOURS SCHEDULED | #value ! | #value ! | #value ! | #value ! | 4:00 | 0:00 | 5:15 |
<tbody>
</tbody>
So far I have been able to get it to calculate my scheduled hours for the week using this formula that I found online:
=(SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24
But I need it to subtract a 30 minute lunch ONLY from the days actually scheduled, and ignore the "OFF and/or VACATION" days.
I was able to calculate my total hours scheduled in the office for the day using this formula that I pieced together:
=(IF(COUNT(B4,C4)=2,MOD(C4-B4,1),0)-1/48)+(IF(COUNT(B5,C5)=2,MOD(C5-B5,1),0)-1/48)+(IF(COUNT(B6,C6)=2,MOD(C6-B6,1),0)-1/48)+(IF(COUNT(B7,C7)=2,MOD(C7-B7,1),0)-1/48)+(IF(COUNT(B8,C8)=2,MOD(C8-B8,1),0)-1/48)+(IF(COUNT(B9,C9)=2,MOD(C9-B9,1),0)-1/48)+(IF(COUNT(B10,C10)=2,MOD(C10-B10,1),0)-1/48)+(IF(COUNT(B11,C11)=2,MOD(C11-B11,1),0)-1/48)
But my issue is that it subtracts a 30 minute lunch from even the "OFF" days, and I get #value ! if I have blanks.
Please help, I'm so close yet sooo far. Thanks for any help