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

[TABLE="width: 1332"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]5-Jan[/TD]
[TD="colspan: 2"]6-Jan[/TD]
[TD="colspan: 2"]7-Jan[/TD]
[TD="colspan: 2"]8-Jan[/TD]
[TD="colspan: 2"]9-Jan[/TD]
[TD="colspan: 2"]10-Jan[/TD]
[TD="colspan: 2"]11-Jan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]FRIDAY[/TD]
[TD="colspan: 2"]SATURDAY[/TD]
[TD="colspan: 2"]SUNDAY[/TD]
[TD="colspan: 2"]MONDAY[/TD]
[TD="colspan: 2"]TUESDAY[/TD]
[TD="colspan: 2"]WEDNESDAY[/TD]
[TD="colspan: 2"]THURSDAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]START[/TD]
[TD]STOP[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12:00 PM[/TD]
[TD]10:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD]5:00 PM[/TD]
[TD]10:15 PM[/TD]
[TD]9.25[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]HOURS SCHEDULED[/TD]
[TD="colspan: 2"]#value ![/TD]
[TD="colspan: 2"]#value ![/TD]
[TD="colspan: 2"]#value ![/TD]
[TD="colspan: 2"]#value ![/TD]
[TD="colspan: 2"]4:00[/TD]
[TD="colspan: 2"]0:00[/TD]
[TD="colspan: 2"]5:15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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