Scheduling - caluclating hours with varying closing times

[kellitenille]

I own a small restaurant. I am redesigning my schedule, and want a column at the end that would tell me how many hours each employee is scheduled for. The issue is that if we close at 9, that doesn't mean the employees walk out the door at 9. They have to clean, count the drawer, prep for the next day, etc. Which means it could be anywhere from 15 mins - 1.5 hours after closing. Therefore I can't list a shift as 4 p.m. - 9 p.m., I need to list it as 4 p.m. - CL (closing). This makes normal, simple, adding and subtracting of times...difficult.

So my question is: is there a formula, that I can apply to the entire sheet, that would tell the total hours column that anywhere it sees "CL" it should use 2100 hours? I would use this for my total hours column, knowing that it would be variable, but would give me base to know approximately how many hours an employee is scheduled for, so that they don't go into overtime. I've tried IF function and stacked IF functions, but I'm obviously not doing something correctly.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]16:00[/TD]
[TD]CL[/TD]
[TD]13:00[/TD]
[TD]CL[/TD]
[TD]13.0[/TD]
[/TR]
[TR]
[TD]Employee B[/TD]
[TD]13:00[/TD]
[TD]CL[/TD]
[TD]15:00[/TD]
[TD]CL[/TD]
[TD]14.0[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Why don't you just put 2100 instead of CL?

 

[TonyUK72]

Let's say you enter 21 (for 9PM) in cell N1 then your formula would be something like this:

=IF(C2="CL",SUM(N$1-B2)+SUM(N$1-D2),IF(E2="CL",SUM(N$1-B2)+SUM(N$1-D2),SUM(C2-B2)+SUM(E2-D2)))


- - - - - - - -

 

[TonyUK72]

Why don't you just put 2100 instead of CL?

I would imagine the reason for CL is so that the staff don't say I finish at 9pm when they look at the rota.

 

[kellitenille]

Why don't you just put 2100 instead of CL?

I explained that in the question.

 

[kellitenille]

Thanks - I will try it ASAP and let you know.