That's an easy one, I guess. Just change Mike's 1,7,... to 0,7,...

=LOOKUP(24*MOD(A1,1),{0,7,15,23},{"Night","Day","E vening","Night"})
--
David Biddulph

"Jon M" wrote in message
...
huge progress by changing from date-time to time with month, etc., and
time
commands.
only rub now is that anything with an hour value of "0," i.e., 0:36 gives
#n/a

getting ready to head to work but if there is an easy fix for this last
step
i would appreciate it.

thanks,

"David Biddulph" wrote:

Presumably you suggested amending the suggested formula to
=LOOKUP(24*A1,{1,7,15,23},{"Night","Day","Evening" ,"Night"}) ?
If so, which times gave you which wrong result?

But it may get a wee bit confused with times like 14:95 :-)
--
David Biddulph

"Jon M" wrote in message
...
I'm probably just slower than most but I can't get it top work.
Using Excel 2007.
Have a column with values like "5:48," etc.

Would like to have a value like:

1 or days for 0700-1495
2 or evening for 1500-2259
3 or nights for 2300-0659

Any further help available?

"Mike H" wrote:

Hi,

Try this,

=LOOKUP(24*A1,{1,7,15},{"Night","Day","Night"})

This will give 2 shifts 07:00 - 15:00 = days
all other time nights
I think you should be able to work out how to do it if you want 3 or 4
different shifts.

Mike

"Jon M" wrote:

Working with spreadshift containing date and times of events during
a
one
month period. I converted the cell content from date and time to
time
(23:00
format).

Now I want to go back and associate events with a given shift, i.e.,
If
B2=(7,0,0) AND time <=(15,0,0), "Days" (or it could just be a
value, ie.,
1)

If I want to assign the value to one of three shifts, can I just
string
all
three formulas together and separate with commas?

Keep getting formula error.
Arghhh.

Thanks in advance for any help.
Help?????