I have a table i import into XL2003 that has date/time values that
appear as, for example, 4/1/2011 7:12:00 AM . The cells with these
date/times are formatted m/d/yyyy h:mm . I want to perform a vlookup
so that if the time is between 7pm and 12am, the lookup will look like
this... vlookup(c3+1,Table1,3), where a time such as
4/1/2011 7:12:00 AM is in c3. if the time is not between 7pm and
12am, the formula will be vlookup(c3,Table1,3). How do i get my
vlookup formula to recognize the time part of the date/time?

Thanks,

Tonso

On Apr 11, 9:38*am, Tonso <wthoma...@hotmail.com> wrote:
[Subject: convert date/time to just time]
> I *have a table i import into XL2003 that has date/time
> values that appear as, for example, *4/1/2011 7:12:00 AM .
> The cells with these date/times are formatted m/d/yyyy h:mm .
> I want to perform a vlookup so that if the time is between
> 7pm and 12am, the lookup will look like this...
>*vlookup(c3+1,Table1,3), where a time such as
> 4/1/2011 7:12:00 AM is in c3. if the time is not between
> 7pm and 12am, the formula will be vlookup(c3,Table1,3).
> How do i get my vlookup formula to recognize the time part
> of the date/time?

Your question is unclear to me, especially in conjunction with the
subject line.

First, you say you want VLOOKUP to consider only(?) the time of day.
But your lookup value is either C3 or C3+1, which changes the
__date__. What point is there in changing the date if you only want
to consider time of day?

Second, it is unclear what the full range of values in Table1 looks
like. Your examples are all for the same date, 4/1/2011. Why not
just enter times of day in Table1 instead of date/time?

I suspect you are asking the wrong question. But without more
concrete examples, it is hard to read between the lines.

Suppose Table1 contains 4/1/2011 7:12 AM and 4/3/2011 7:12 AM, and C3
contains 4/3/2011 7:12 AM. Which entry should VLOOKUP match? Do you
truly want to ignore the date?(!)

Then suppose Table1 contains 4/4/2011 7:00 AM and 4/5/2011 6:00 AM,
and C3 contains 4/4/2011 9:00 AM. Which entry should VLOOKUP match?

As you know, VLOOKUP normally returns the largest value less than or
equal to the lookup value if the 4th parameter is TRUE or missing, as
it is in your examples.

But that depends on Table1 being in ascending order in the 1st
column. That condition is met for the date/times. But if you exclude
the date, the times are no longer in ascending order[*].

-----
[*] The following array formula does a lookup without regard for
date. It is __not__ intended as a solution, since there are so many
unanswered questions regarding your intent. For that reason, I am not
explaining how to enter an array formula.

=INDEX(table1,MATCH(MOD(C3,1),MOD(INDEX(table1,0,1),1)),2)

On Apr 11, 12:01*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> Your question is unclear to me, especially in conjunction
> with the subject line.
> First, [....]

I edited out my truly first question because I thought I had misread.
Now I realize that I read things correctly the first time. So my
third question is....

You wrote "if the time is between 7pm and 12am", but your example is
"7:12:00 AM is in c3", which is not between 7pm and 12am.

Perhaps you mean between 7pm and 12pm (noon), not between 7pm and 12am
(midnight). Or perhaps you mean between 12am (midnight) and 7pm,
assuming that a swing shift starts after 7pm and ends after 12am
(midnight).

Even so, it might not be correct to increment C3 by 1 (day) for the
purpose of the lookup. That depends on the content of the 1st column
of Table1, which is unclear as I mentioned previously.

I wrote:
> I suspect you are asking the wrong question. *But without
> more concrete examples, it is hard to read between the lines.

Taking a wild-a.s.s guess, I suspect that your problem is solved by
the following.

First, Table1 should contain only times of day in ascending order in
the 1st column.

Then you might write VLOOKUP(MOD(C3,1),Table1,3).

However, there are binary arithmetic issues with using MOD(C3,1) to
extract time of day. It would be more reliable to write --
TEXT(C3,"hh:mm:ss"). The double-negative (--) converts the text
result to a number, the way that Excel represents time.

Clarification....

On Apr 11, 12:22*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> Perhaps you mean between 7pm and 12pm (noon)

That is: between 7pm and 12pm (noon) the next day.

On Apr 11, 3:01*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Apr 11, 9:38*am, Tonso <wthoma...@hotmail.com> wrote:
> [Subject: *convert date/time to just time]
>
> > I *have a table i import into XL2003 that has date/time
> > values that appear as, for example, *4/1/2011 7:12:00 AM .
> > The cells with these date/times are formatted m/d/yyyy h:mm .
> > I want to perform a vlookup so that if the time is between
> > 7pm and 12am, the lookup will look like this...
> >*vlookup(c3+1,Table1,3), where a time such as
> > 4/1/2011 7:12:00 AM is in c3. if the time is not between
> > 7pm and 12am, the formula will be vlookup(c3,Table1,3).
> > How do i get my vlookup formula to recognize the time part
> > of the date/time?
>
> Your question is unclear to me, especially in conjunction with the
> subject line.
>
> First, you say you want VLOOKUP to consider only(?) the time of day.
> But your lookup value is either C3 or C3+1, which changes the
> __date__. *What point is there in changing the date if you only want
> to consider time of day?
>
> Second, it is unclear what the full range of values in Table1 looks
> like. *Your examples are all for the same date, 4/1/2011. *Why not
> just enter times of day in Table1 instead of date/time?
>
> I suspect you are asking the wrong question. *But without more
> concrete examples, it is hard to read between the lines.
>
> Suppose Table1 contains 4/1/2011 7:12 AM and 4/3/2011 7:12 AM, and C3
> contains 4/3/2011 7:12 AM. *Which entry should VLOOKUP match? *Do you
> truly want to ignore the date?(!)
>
> Then suppose Table1 contains 4/4/2011 7:00 AM and 4/5/2011 6:00 AM,
> and C3 contains 4/4/2011 9:00 AM. *Which entry should VLOOKUP match?
>
> As you know, VLOOKUP normally returns the largest value less than or
> equal to the lookup value if the 4th parameter is TRUE or missing, as
> it is in your examples.
>
> But that depends on Table1 being in ascending order in the 1st
> column. *That condition is met for the date/times. *But if you exclude
> the date, the times are no longer in ascending order[*].
>
> -----
>
>[*] The following array formula does a lookup without regard for
> date. *It is __not__ intended as a solution, since there are so many
> unanswered questions regarding your intent. *For that reason, I am not
> explaining how to enter an array formula.
>
> =INDEX(table1,MATCH(MOD(C3,1),MOD(INDEX(table1,0,1),1)),2)

Thanks!

Tonso