convert date/time to just time

[Tonso]

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

 

[joeu2004]

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)

 

[joeu2004]

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

 

[joeu2004]

Clarification....

Perhaps you mean between 7pm and 12pm (noon)

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

 

[Tonso]

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