Help needed with timesheet/shceduling formulas

[Waiting2Excel]

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!".

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

 

[bellman101]

Try:

=((SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24) - (0.5*COUNTIFS($B$3:$O$3,"START",B4:O4,">"&DATE(2000,1,1)))

EDIT: Sorry - misread. if "OFF" is in the shift times, then try:

=((SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24) - (0.5*COUNTIFS($B$3:$O$3,"START",B4:O4,">"&DATE(2000,1,1),B4:O4,"<>OFF"))

Cheers
JB

 

[Waiting2Excel]

Try:

=((SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24) - (0.5*COUNTIFS($B$3:$O$3,"START",B4:O4,">"&DATE(2000,1,1)))

EDIT: Sorry - misread. if "OFF" is in the shift times, then try:

=((SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24) - (0.5*COUNTIFS($B$3:$O$3,"START",B4:O4,">"&DATE(2000,1,1),B4:O4,"<>OFF"))

Cheers
JB

I tried that, however it returns only the total hour sum, it does not subtract the 30 minute lunch.

 

[bellman101]

I thought this after I hit post - are the times just "09:00" or in the formula bar are they actually "dd/mm/yyyy 09:00"?

I normally work with formatted datetimes, and didn't think to check.

If that is the case, then the COUNTIFS() will result in 0, so no breaks.

Change it to:
=((SUMIF($B$3:$O$3,"STOP",B4:O4)-SUMIF($B$3:$O$3,"START",B4:O4))*24) - (0.5*COUNTIFS($B$3:$O$3,"START",B4:O4,">"&0,B4:O4,"<>OFF"))

Cheers
JB

 

[Waiting2Excel]

WHOOO MAN, THAT WORKED LIKE A CHARM! THANKS!

Any suggestions on my formula for calculating my daily scheduled hours? Can your same formula be tweaked to provide a daily total per column?

 

[bellman101]

WHOOO MAN, THAT WORKED LIKE A CHARM! THANKS!

Any suggestions on my formula for calculating my daily scheduled hours? Can your same formula be tweaked to provide a daily total per column?

Sure it can! It's simpler though, as you don't need to worry about START and STOP so no SUMIFS required, 1 column is 1, 1 is the other so:

[COLOR=#333333][COLOR=#333333][I]=((SUM(C4:C11)-SUM(B4:B11))*24)-(COUNT(B4:B11)*0.5)    ' COUNT will only count numeric values, so "OFF" will be ignored, but times are counted as numeric so are included.[/I][/COLOR][/COLOR][COLOR=#333333]
[/COLOR]

I've not tested it, but I think that's right for your file.

Cheers
JB

 

[Waiting2Excel]

Again, you are a savior! Thanks a lot, you have made my day!

 

[bellman101]

You are more than welcome, glad to help