[SOLVED] Biff or anyone- A further challenge for work schedule creation

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

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

"Paul" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Paul" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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


"Paul" wrote:

> 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

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

"pinmaster" wrote:

> 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
>
>
> "Paul" wrote:
>
> > 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

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.

"pinmaster" wrote:

> 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
>
> "pinmaster" wrote:
>
> > 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
> >
> >
> > "Paul" wrote:
> >
> > > 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

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

"Paul" wrote:

> 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.
>
> "pinmaster" wrote:
>
> > 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
> >
> > "pinmaster" wrote:
> >
> > > 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
> > >
> > >
> > > "Paul" wrote:
> > >
> > > > 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 & 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

"Paul" wrote:

> 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

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

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" wrote:

>
> 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
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=499123
>
>

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


JG

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

"pinmaster" wrote:

>
> Wow.....that is strange....Where did you get your list from? And how did
> you create it?
>
>
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=499123
>
>

Hi Paul,

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

=$B$42:$B$60

Regards
JG

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

"pinmaster" wrote:

>
> Hi Paul,
>
> I don't know why I didn't think about this last night but you have to
> make the range reference absolute.
>
> =$B$42:$B$60
>
> Regards
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=499123
>
>