Biff or anyone- A further challenge for work schedule creation

[Guest]

Recently, you helped me out on the Microsoft Office discussion board, and
that was greatly appreciated. I need to take this scheduling spreadsheet to
the next level, though, and I was hoping to impose upon you one more time for
some help. The following formula is currently being used to figure the number
of hours worked daily less a 30 minute lunch:

=IF(COUNT(B3:C3)<=0,0,TEXT((C3-B3)-TIME(0,30,0),"h:mm"))

Here's where I really need the help. I would like to work something into the
formula for the following info:

work 5.5 hours or less = no lunch

work 9 - 9.75 hours = 45 minute lunch

work 10 or more hours = 60 minute lunch

The 5.5 hours worked formula is not as important to me as the others, but it
would be nice to have.

Please let me know if there is any other info you might need, or if you even
want to take this on.

Thank you,

Paul

 

[Biff]

Hi!

Let me see if I can find your other post to refresh my memory. I vaguely
remember. One minor point until then:

=IF(COUNT(B3:C3)<=0,0,TEXT((C3-B3)-TIME(0,30,0),"h:mm"))

Since the COUNT cannot be less than 0, there's no need for the less than (<)
comparison but as is, has no impact on the formula. It might also be better
to make sure there are at least 2 entries in B3:C3 (time entries?). Let me
see if I can find the other post. I'm wondering why you want(ed) the result
as a TEXT value?

Biff

 

[Biff]

Hi!

Here's that other thread:

http://tinyurl.com/9h8c5

There's a slight "glitch" in your time increments:

work 9 - 9.75 hours = 45 minute lunch
work 10 or more hours = 60 minute lunch

What happens if someone works more than 9.75 hrs but less than or equal
to10?

Here's what I came up with:

<=5.5 = 0
<=9.0 = 30
<=10.0 = 45

10 = 60

=IF(COUNT(B3:C3)<>2,0,TEXT((C3-B3)-IF((C3-B3)*24<=5.5,0,IF((C3-B3)*24<=9,TIME(0,30,0),IF((C3-B3)*24<=10,TIME(0,45,0),TIME(1,0,0)))),"h:mm"))

Still can't figure out why you want the result as TEXT!

Biff

 

[Guest]

Another way:
=IF(COUNT(B3:C3)<=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{5.5,9,10},{30,45,60}),0),"h:mm"))


JG

 

[Guest]

Slight modification to my formula
=IF(COUNT(B3:C3)<=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0),"h:mm"))
or
=IF(COUNT(B3:C3)<=0,0,(C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0))
with cell formatted as h:mm

JG

 

[Guest]

I'm not sure if this makes any difference in any of the formulas, but I am
using validated dropdowns in the cells (ex B3 & C3) that contain AM & PM.
None of these formulas have given me any results. Thank you to both of you
for your efforts. I'll check back again.

 

[Guest]

Hi Paul, just tested the formula with validation lists in B3 and C3 the lists
were taken from a range of cells with different time intervals and with the
cells formatted as h:mm AM/PM, it had no effect on the results, the formula
worked like a charm. If your formula worked before I see no reason why it
wouldn't work now. It is basically the same except for the LOOKUP bit. Maybe
Biff has an answer.

Regards
JG

 

[Guest]

Biff & JG,

Thank you both so very much for all your help!!! After some minor tweaking
of your suggestions, I have the formula working just the way I want it. Now I
have a new, minor dilema. My company has added Sunday hours and an idea I
originally wanted to incorporate (scheduled to work less than 6 hours = 0
time for lunch) really comes into play, as we will only be open 4 hours on
Sunday's. Also, do either of you know of an easy way of copying a validated
cell to new cells. The process I used was very cumbersome; validating each
cell. Once again, any help you can offer will be greatly appreciated.

Paul

 

[pinmaster]

Hi Paul,

Not sure what your final formula looks like but the one I gave you
would account for hours lesss than 5.5 giving 0 time for lunch break,
and since you would open for just 4 hours on sundays there should be no
problem. As for the second part, validation list can be copied and
pasted at will. Or you can select a range of cells, create your list
and it will insert the list in all the selected cells.

HTH
JG

 

[Guest]

JG,

You're right, it does account for a 4 hour shift. I attempted it at the
office and something about that didn't work right. When I tried it at home,
it worked fine. As for the copying of validated cells- I just attempted it,
and for every cell down I copied it to, it removed one line of my dropdown.
My dropdown contains half hour increments ranging from 7:00 AM to 7:00 PM.
When I copy it one cell below, the dropdown starts at 7:30 AM, then 8:00 AM
next cell down, etc.. Any ideas?

Thanks,

Paul

 

[pinmaster]

Wow.....that is strange....Where did you get your list from? And how did
you create it?


JG

 

[Guest]

From a downward range of cells (b41,b42,b43,etc) outside my work area.

 

[pinmaster]

Hi Paul,

I don't know why I didn't think about this last night but you have t
make the range reference absolute.

=$B$42:$B$60

Regards
J

 

[Guest]

Thank you both very much!!!!

This is going to make a lot of our manager's lives a lot easier. Scheduling
has always been a very time consuming process, but with the great help you
both provided me, scheduling time has probably been cut in half. And, as an
added bonus, I've learned a lot more about some of Excel's functions.

Paul