software

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)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))

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:

gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))

Since the COUNT cannot be less than 0, there's no need for the less than (lt;)
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

quot;Paulquot; gt; wrote in message
...
gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; that was greatly appreciated. I need to take this scheduling spreadsheet
gt; to
gt; the next level, though, and I was hoping to impose upon you one more time
gt; for
gt; some help. The following formula is currently being used to figure the
gt; number
gt; of hours worked daily less a 30 minute lunch:
gt;
gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt;
gt; Here's where I really need the help. I would like to work something into
gt; the
gt; formula for the following info:
gt;
gt; work 5.5 hours or less = no lunch
gt;
gt; work 9 - 9.75 hours = 45 minute lunch
gt;
gt; work 10 or more hours = 60 minute lunch
gt;
gt; The 5.5 hours worked formula is not as important to me as the others, but
gt; it
gt; would be nice to have.
gt;
gt; Please let me know if there is any other info you might need, or if you
gt; even
gt; want to take this on.
gt;
gt; Thank you,
gt;
gt; Paul
Hi!

Here's that other thread:

tinyurl.com/9h8c5

There's a slight quot;glitchquot; in your time increments:

gt; work 9 - 9.75 hours = 45 minute lunch
gt; 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:

lt;=5.5 = 0
lt;=9.0 = 30
lt;=10.0 = 45
gt;10 = 60

=IF(COUNT(B3:C3)lt;gt;2,0,TEXT((C3-B3)-IF((C3-B3)*24lt;=5.5,0,IF((C3-B3)*24lt;=9,TIME(0,30,0),IF((C3-B3)*24lt;=10,TIME(0,45,0),TIME(1,0,0)))),quot;h:mmquot;))

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

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Let me see if I can find your other post to refresh my memory. I vaguely
gt; remember. One minor point until then:
gt;
gt;gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt;
gt; Since the COUNT cannot be less than 0, there's no need for the less than
gt; (lt;) comparison but as is, has no impact on the formula. It might also be
gt; better to make sure there are at least 2 entries in B3:C3 (time entries?).
gt; Let me see if I can find the other post. I'm wondering why you want(ed)
gt; the result as a TEXT value?
gt;
gt; Biff
gt;
gt; quot;Paulquot; gt; wrote in message
gt; ...
gt;gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt;gt; that was greatly appreciated. I need to take this scheduling spreadsheet
gt;gt; to
gt;gt; the next level, though, and I was hoping to impose upon you one more time
gt;gt; for
gt;gt; some help. The following formula is currently being used to figure the
gt;gt; number
gt;gt; of hours worked daily less a 30 minute lunch:
gt;gt;
gt;gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt;gt;
gt;gt; Here's where I really need the help. I would like to work something into
gt;gt; the
gt;gt; formula for the following info:
gt;gt;
gt;gt; work 5.5 hours or less = no lunch
gt;gt;
gt;gt; work 9 - 9.75 hours = 45 minute lunch
gt;gt;
gt;gt; work 10 or more hours = 60 minute lunch
gt;gt;
gt;gt; The 5.5 hours worked formula is not as important to me as the others, but
gt;gt; it
gt;gt; would be nice to have.
gt;gt;
gt;gt; Please let me know if there is any other info you might need, or if you
gt;gt; even
gt;gt; want to take this on.
gt;gt;
gt;gt; Thank you,
gt;gt;
gt;gt; Paul
gt;
gt;
Another way:
=IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{5.5,9,10},{30,45,60}),0),quot;h:mmquot;))JGquot;Paulquot; wrote:

gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; that was greatly appreciated. I need to take this scheduling spreadsheet to
gt; the next level, though, and I was hoping to impose upon you one more time for
gt; some help. The following formula is currently being used to figure the number
gt; of hours worked daily less a 30 minute lunch:
gt;
gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt;
gt; Here's where I really need the help. I would like to work something into the
gt; formula for the following info:
gt;
gt; work 5.5 hours or less = no lunch
gt;
gt; work 9 - 9.75 hours = 45 minute lunch
gt;
gt; work 10 or more hours = 60 minute lunch
gt;
gt; The 5.5 hours worked formula is not as important to me as the others, but it
gt; would be nice to have.
gt;
gt; Please let me know if there is any other info you might need, or if you even
gt; want to take this on.
gt;
gt; Thank you,
gt;
gt; Paul

Slight modification to my formula
=IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0),quot;h:mmquot;))
or
=IF(COUNT(B3:C3)lt;=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

quot;pinmasterquot; wrote:

gt; Another way:
gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{5.5,9,10},{30,45,60}),0),quot;h:mmquot;))
gt;
gt;
gt; JG
gt;
gt;
gt; quot;Paulquot; wrote:
gt;
gt; gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; gt; that was greatly appreciated. I need to take this scheduling spreadsheet to
gt; gt; the next level, though, and I was hoping to impose upon you one more time for
gt; gt; some help. The following formula is currently being used to figure the number
gt; gt; of hours worked daily less a 30 minute lunch:
gt; gt;
gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt; gt;
gt; gt; Here's where I really need the help. I would like to work something into the
gt; gt; formula for the following info:
gt; gt;
gt; gt; work 5.5 hours or less = no lunch
gt; gt;
gt; gt; work 9 - 9.75 hours = 45 minute lunch
gt; gt;
gt; gt; work 10 or more hours = 60 minute lunch
gt; gt;
gt; gt; The 5.5 hours worked formula is not as important to me as the others, but it
gt; gt; would be nice to have.
gt; gt;
gt; gt; Please let me know if there is any other info you might need, or if you even
gt; gt; want to take this on.
gt; gt;
gt; gt; Thank you,
gt; gt;
gt; gt; 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 amp; C3) that contain AM amp; PM.
None of these formulas have given me any results. Thank you to both of you
for your efforts. I'll check back again.

quot;pinmasterquot; wrote:

gt; Slight modification to my formula
gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0),quot;h:mmquot;))
gt; or
gt; =IF(COUNT(B3:C3)lt;=0,0,(C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0))
gt; with cell formatted as h:mm
gt;
gt; JG
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt; Another way:
gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{5.5,9,10},{30,45,60}),0),quot;h:mmquot;))
gt; gt;
gt; gt;
gt; gt; JG
gt; gt;
gt; gt;
gt; gt; quot;Paulquot; wrote:
gt; gt;
gt; gt; gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; gt; gt; that was greatly appreciated. I need to take this scheduling spreadsheet to
gt; gt; gt; the next level, though, and I was hoping to impose upon you one more time for
gt; gt; gt; some help. The following formula is currently being used to figure the number
gt; gt; gt; of hours worked daily less a 30 minute lunch:
gt; gt; gt;
gt; gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt; gt; gt;
gt; gt; gt; Here's where I really need the help. I would like to work something into the
gt; gt; gt; formula for the following info:
gt; gt; gt;
gt; gt; gt; work 5.5 hours or less = no lunch
gt; gt; gt;
gt; gt; gt; work 9 - 9.75 hours = 45 minute lunch
gt; gt; gt;
gt; gt; gt; work 10 or more hours = 60 minute lunch
gt; gt; gt;
gt; gt; gt; The 5.5 hours worked formula is not as important to me as the others, but it
gt; gt; gt; would be nice to have.
gt; gt; gt;
gt; gt; gt; Please let me know if there is any other info you might need, or if you even
gt; gt; gt; want to take this on.
gt; gt; gt;
gt; gt; gt; Thank you,
gt; gt; gt;
gt; gt; gt; 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

quot;Paulquot; wrote:

gt; I'm not sure if this makes any difference in any of the formulas, but I am
gt; using validated dropdowns in the cells (ex B3 amp; C3) that contain AM amp; PM.
gt; None of these formulas have given me any results. Thank you to both of you
gt; for your efforts. I'll check back again.
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt; Slight modification to my formula
gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0),quot;h:mmquot;))
gt; gt; or
gt; gt; =IF(COUNT(B3:C3)lt;=0,0,(C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{0,5.5,9,10},{0,30,45,60}),0))
gt; gt; with cell formatted as h:mm
gt; gt;
gt; gt; JG
gt; gt;
gt; gt; quot;pinmasterquot; wrote:
gt; gt;
gt; gt; gt; Another way:
gt; gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,LOOKUP((C3-B3)*24,{5.5,9,10},{30,45,60}),0),quot;h:mmquot;))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; JG
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Paulquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; gt; gt; gt; that was greatly appreciated. I need to take this scheduling spreadsheet to
gt; gt; gt; gt; the next level, though, and I was hoping to impose upon you one more time for
gt; gt; gt; gt; some help. The following formula is currently being used to figure the number
gt; gt; gt; gt; of hours worked daily less a 30 minute lunch:
gt; gt; gt; gt;
gt; gt; gt; gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt; gt; gt; gt;
gt; gt; gt; gt; Here's where I really need the help. I would like to work something into the
gt; gt; gt; gt; formula for the following info:
gt; gt; gt; gt;
gt; gt; gt; gt; work 5.5 hours or less = no lunch
gt; gt; gt; gt;
gt; gt; gt; gt; work 9 - 9.75 hours = 45 minute lunch
gt; gt; gt; gt;
gt; gt; gt; gt; work 10 or more hours = 60 minute lunch
gt; gt; gt; gt;
gt; gt; gt; gt; The 5.5 hours worked formula is not as important to me as the others, but it
gt; gt; gt; gt; would be nice to have.
gt; gt; gt; gt;
gt; gt; gt; gt; Please let me know if there is any other info you might need, or if you even
gt; gt; gt; gt; want to take this on.
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you,
gt; gt; gt; gt;
gt; gt; gt; gt; Paul

Biff amp; 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

quot;Paulquot; wrote:

gt; Recently, you helped me out on the Microsoft Office discussion board, and
gt; that was greatly appreciated. I need to take this scheduling spreadsheet to
gt; the next level, though, and I was hoping to impose upon you one more time for
gt; some help. The following formula is currently being used to figure the number
gt; of hours worked daily less a 30 minute lunch:
gt;
gt; =IF(COUNT(B3:C3)lt;=0,0,TEXT((C3-B3)-TIME(0,30,0),quot;h:mmquot;))
gt;
gt; Here's where I really need the help. I would like to work something into the
gt; formula for the following info:
gt;
gt; work 5.5 hours or less = no lunch
gt;
gt; work 9 - 9.75 hours = 45 minute lunch
gt;
gt; work 10 or more hours = 60 minute lunch
gt;
gt; The 5.5 hours worked formula is not as important to me as the others, but it
gt; would be nice to have.
gt;
gt; Please let me know if there is any other info you might need, or if you even
gt; want to take this on.
gt;
gt; Thank you,
gt;
gt; 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--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=499123JG,

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

quot;pinmasterquot; wrote:

gt;
gt; Hi Paul,
gt;
gt; Not sure what your final formula looks like but the one I gave you
gt; would account for hours lesss than 5.5 giving 0 time for lunch break,
gt; and since you would open for just 4 hours on sundays there should be no
gt; problem. As for the second part, validation list can be copied and
gt; pasted at will. Or you can select a range of cells, create your list
gt; and it will insert the list in all the selected cells.
gt;
gt; HTH
gt; JG
gt;
gt;
gt; --
gt; pinmaster
gt; ------------------------------------------------------------------------
gt; pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
gt; View this thread: www.excelforum.com/showthread...hreadid=499123
gt;
gt;