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?????

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?????

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?????

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?????

Hmm.
They all come put to be "night."
I guess A1 is the cell containing the time.

The variable in this cell used to be date & time but I changed it with the
format time to just show the time.
Reading about this formula I'll bet the date information is still there even
though it is not displayed and that is what throwing this off.

I will keep looking but is ther an easy way to change the value from
date-time to just time or am I off track...?

"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?????

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

"Jon M" wrote in message
...
Hmm.
They all come put to be "night."
I guess A1 is the cell containing the time.

The variable in this cell used to be date & time but I changed it with the
format time to just show the time.
Reading about this formula I'll bet the date information is still there
even
though it is not displayed and that is what throwing this off.

I will keep looking but is ther an easy way to change the value from
date-time to just time or am I off track...?

"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?????

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?????

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?????

Thanks, that worked!

What if I need to get more granular in my analysis and plot by 2 or 4 hour
intervals, i.e., 0001-0159, 0200-0359, etc.?


"David Biddulph" wrote:

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?????

Change it in the same way as you did when you added an extra shift earlier.
--
David Biddulph

"Jon M" wrote in message
...
Thanks, that worked!

What if I need to get more granular in my analysis and plot by 2 or 4 hour
intervals, i.e., 0001-0159, 0200-0359, etc.?


"David Biddulph" wrote:

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?????