I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code
for any times within that range of time:
5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6

BUT, I need to be able to allow for multiple answers in one cell. Here's an
example of what I need it to do:

12:00 AM 8:55 AM 1
8:55 AM 9:05 AM 1, 2
9:05 AM 9:55 AM 2
9:55 AM 10:05 AM 2
10:05 AM 10:25 AM 2
10:25 AM 12:00 PM 2, 3
12:00 PM 12:30 PM 3

This is for a research project that has do be done by Monday, can someone
help? I'm mildly computer literate but would need mostly laymans language.
HELP!


--
Elizabeth

Presumably, you won't have any times before 5:00 am or after 7:00pm?
This is a nice little problem, but it's a bit late here in the UK so I
haven't got time to delve into it. If you haven't had any solutions by
the morning, I'll look at it then. I think you just need a simple
table of times and codes, and an IF to give you the composite codes.

Pete

On Oct 26, 1:25 am, Elizabeth
wrote:
I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code
for any times within that range of time:
5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6

BUT, I need to be able to allow for multiple answers in one cell. Here's an
example of what I need it to do:

12:00 AM 8:55 AM 1
8:55 AM 9:05 AM 1, 2
9:05 AM 9:55 AM 2
9:55 AM 10:05 AM 2
10:05 AM 10:25 AM 2
10:25 AM 12:00 PM 2, 3
12:00 PM 12:30 PM 3

This is for a research project that has do be done by Monday, can someone
help? I'm mildly computer literate but would need mostly laymans language.
HELP!

--
Elizabeth

Actually, I have codes for every time of the day and night. I just didn't
want send a gigantic message! Is that something that is needed to answer the
question? I tried to figure out an IF formula but just can't get it to work.

Thank you, I look forward to having help!
Elizabeth
--
Elizabeth


"Pete_UK" wrote:

Presumably, you won't have any times before 5:00 am or after 7:00pm?
This is a nice little problem, but it's a bit late here in the UK so I
haven't got time to delve into it. If you haven't had any solutions by
the morning, I'll look at it then. I think you just need a simple
table of times and codes, and an IF to give you the composite codes.

Pete

On Oct 26, 1:25 am, Elizabeth
wrote:
I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code
for any times within that range of time:
5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6

BUT, I need to be able to allow for multiple answers in one cell. Here's an
example of what I need it to do:

12:00 AM 8:55 AM 1
8:55 AM 9:05 AM 1, 2
9:05 AM 9:55 AM 2
9:55 AM 10:05 AM 2
10:05 AM 10:25 AM 2
10:25 AM 12:00 PM 2, 3
12:00 PM 12:30 PM 3

This is for a research project that has do be done by Monday, can someone
help? I'm mildly computer literate but would need mostly laymans language.
HELP!

--
Elizabeth

I was hoping you would give a bit more detail, rather than just tell
me that there is more detail. I can only work with the example data
you have given, so you may have to adapt what follows to suit your
particular circumstances.

First of all, you need to set up a small table somewhere, made up of
the start times of your ranges and the codes that go with them.
Suppose you put this in X1:Y7 of your sheet, and it looks like this:

0:00 AM 0
5:01 AM 1
9:01 AM 2
11:01 AM 3
1:01 PM 4
3:01 PM 5
5:01 PM 6

Assume also that your starting times are in column A with finish times
in column B, and that the data starts on row 2. You will need a
formula like the following:

=VLOOKUP(A2,X$1:Y$7,2)&IF(VLOOKUP(B2,X$1:Y$7,2)<V LOOKUP(A2,X$1:Y
$7,2),", "&VLOOKUP(B2,X$1:Y$7,2),"")

(all one formula - be wary of spurious line breaks in the newsgroups).
Copy the formula down for as many rows as you need.

Hope this helps.

Pete


On Oct 26, 3:11 am, Elizabeth
wrote:
Actually, I have codes for every time of the day and night. I just didn't
want send a gigantic message! Is that something that is needed to answer the
question? I tried to figure out an IF formula but just can't get it to work.

Thank you, I look forward to having help!
Elizabeth
--
Elizabeth



"Pete_UK" wrote:
Presumably, you won't have any times before 5:00 am or after 7:00pm?
This is a nice little problem, but it's a bit late here in the UK so I
haven't got time to delve into it. If you haven't had any solutions by
the morning, I'll look at it then. I think you just need a simple
table of times and codes, and an IF to give you the composite codes.

Pete

On Oct 26, 1:25 am, Elizabeth
wrote:
I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code
for any times within that range of time:
5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6

BUT, I need to be able to allow for multiple answers in one cell. Here's an
example of what I need it to do:

12:00 AM 8:55 AM 1
8:55 AM 9:05 AM 1, 2
9:05 AM 9:55 AM 2
9:55 AM 10:05 AM 2
10:05 AM 10:25 AM 2
10:25 AM 12:00 PM 2, 3
12:00 PM 12:30 PM 3

This is for a research project that has do be done by Monday, can someone
help? I'm mildly computer literate but would need mostly laymans language.
HELP!

--
Elizabeth- Hide quoted text -

- Show quoted text -

I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one
column, it automatically enters a diff number, in this case 99, in the next
column. I'm trying to use the IF function but I just can't figure the darn
thing out.

Thanks Again!!!
E
--
Elizabeth


"Elizabeth" wrote:

I need to figure out how to assign a code in a separate cell to a range of
time. Here's part of the codes I need to use (the last column being the code
for any times within that range of time:
5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6

BUT, I need to be able to allow for multiple answers in one cell. Here's an
example of what I need it to do:

12:00 AM 8:55 AM 1
8:55 AM 9:05 AM 1, 2
9:05 AM 9:55 AM 2
9:55 AM 10:05 AM 2
10:05 AM 10:25 AM 2
10:25 AM 12:00 PM 2, 3
12:00 PM 12:30 PM 3

This is for a research project that has do be done by Monday, can someone
help? I'm mildly computer literate but would need mostly laymans language.
HELP!


--
Elizabeth

Assume your numbers are in column E, starting on row 2. Put this
formula in F2:

=IF(E2=2,99,E2)

and copy down. This just replaces 2 in column E with 99 - other values
remain the same. Is this what you want?

Hope this helps.

Pete

On Oct 26, 4:45 am, Elizabeth
wrote:
I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one
column, it automatically enters a diff number, in this case 99, in the next
column. I'm trying to use the IF function but I just can't figure the darn
thing out.

Thanks Again!!!
E
--
Elizabeth

Yes, the second answer is great, thanks!

First one: that why I asked. : )

Here is the full detail, I'd need, as the example in my first msg, to be
able to have a second code auto-populate in the appropriate column as well.
So if two time span categories overlap there could be up to 3 or 4 codes.

Thank you so much!!!!
E




5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6
7:01 PM 9:00 PM 7
9:01 PM 11:00 PM 8
11:01 PM 1:00 AM 9
1:01 AM 5:00 AM 10






--
Elizabeth


"Pete_UK" wrote:

Assume your numbers are in column E, starting on row 2. Put this
formula in F2:

=IF(E2=2,99,E2)

and copy down. This just replaces 2 in column E with 99 - other values
remain the same. Is this what you want?

Hope this helps.

Pete

On Oct 26, 4:45 am, Elizabeth
wrote:
I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one
column, it automatically enters a diff number, in this case 99, in the next
column. I'm trying to use the IF function but I just can't figure the darn
thing out.

Thanks Again!!!
E
--
Elizabeth

Did you see my earlier response? With this extra detail, you will need
to amend the table slightly, as follows:

0:00 AM 9
1:00 AM 10
5:01 AM 1
9:01 AM 2
11:01 AM 3
1:01 PM 4
3:01 PM 5
5:01 PM 6
7:01 PM 7
9:01 PM 8
11:01 PM 9

so your table would now occupy X1:Y11, and the formula I gave you
earlier would become:

=VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y
$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"")

This covers the examples you gave in your first post, but are you now
saying that if the start time was 8:55 am and finish time was 11:15 am
then you would like to see 1, 2, 3 returned from the formula?

Pete

On Oct 26, 5:00 pm, Elizabeth
wrote:
Yes, the second answer is great, thanks!

First one: that why I asked. : )

Here is the full detail, I'd need, as the example in my first msg, to be
able to have a second code auto-populate in the appropriate column as well.
So if two time span categories overlap there could be up to 3 or 4 codes.

Thank you so much!!!!
E

5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6
7:01 PM 9:00 PM 7
9:01 PM 11:00 PM 8
11:01 PM 1:00 AM 9
1:01 AM 5:00 AM 10

--
Elizabeth



"Pete_UK" wrote:
Assume your numbers are in column E, starting on row 2. Put this
formula in F2:

=IF(E2=2,99,E2)

and copy down. This just replaces 2 in column E with 99 - other values
remain the same. Is this what you want?

Hope this helps.

Pete

On Oct 26, 4:45 am, Elizabeth
wrote:
I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one
column, it automatically enters a diff number, in this case 99, in the next
column. I'm trying to use the IF function but I just can't figure the darn
thing out.

Thanks Again!!!
E
--
Elizabeth- Hide quoted text -

- Show quoted text -

Ok great! Thank you for your help! If you look back to my original post
you'll see that, yes, I need to be able to have the range of codes. See first
post for example. Thank you, I will try this and hope it works. Even that
would be a great help!

I sent you the rest of the table because you said you were hoping I would,
thank you for looking at it again!...I feel like we are missing part of each
other's communique. : ) Thank you SO much!
E
--
Elizabeth


"Pete_UK" wrote:

Did you see my earlier response? With this extra detail, you will need
to amend the table slightly, as follows:

0:00 AM 9
1:00 AM 10
5:01 AM 1
9:01 AM 2
11:01 AM 3
1:01 PM 4
3:01 PM 5
5:01 PM 6
7:01 PM 7
9:01 PM 8
11:01 PM 9

so your table would now occupy X1:Y11, and the formula I gave you
earlier would become:

=VLOOKUP(A2,X$1:Y$11,2)&IF(VLOOKUP(B2,X$1:Y$11,2)< VLOOKUP(A2,X$1:Y
$11,2),", "&VLOOKUP(B2,X$1:Y$11,2),"")

This covers the examples you gave in your first post, but are you now
saying that if the start time was 8:55 am and finish time was 11:15 am
then you would like to see 1, 2, 3 returned from the formula?

Pete

On Oct 26, 5:00 pm, Elizabeth
wrote:
Yes, the second answer is great, thanks!

First one: that why I asked. : )

Here is the full detail, I'd need, as the example in my first msg, to be
able to have a second code auto-populate in the appropriate column as well.
So if two time span categories overlap there could be up to 3 or 4 codes.

Thank you so much!!!!
E

5:01 AM 9:00 AM 1
9:01 AM 11:00 AM 2
11:01 AM 1:00 PM 3
1:01 PM 3:00 PM 4
3:01 PM 5:00 PM 5
5:01 PM 7:00 PM 6
7:01 PM 9:00 PM 7
9:01 PM 11:00 PM 8
11:01 PM 1:00 AM 9
1:01 AM 5:00 AM 10

--
Elizabeth



"Pete_UK" wrote:
Assume your numbers are in column E, starting on row 2. Put this
formula in F2:

=IF(E2=2,99,E2)

and copy down. This just replaces 2 in column E with 99 - other values
remain the same. Is this what you want?

Hope this helps.

Pete

On Oct 26, 4:45 am, Elizabeth
wrote:
I also have a simpler issue but still can't make the darn thing work. I need
to create a formula so that if there is a certain number, such as a 2 in one
column, it automatically enters a diff number, in this case 99, in the next
column. I'm trying to use the IF function but I just can't figure the darn
thing out.

Thanks Again!!!
E
--
Elizabeth- Hide quoted text -

- Show quoted text -