Vlookup and date format problem

[dazman]

Hi,

I have yesterdays date in a spreadsheet which is obtained by the
function =NOW()-1. It’s shown on the spreadsheet with a Date and a time
and I didn’t want the time so I formatted the cell to only show the
date.

I then want to refer to this Cell with yesterdays date, which is in B2
in my main spreadsheet with a VLOOKUP as follows:
=VLOOKUP(B2,'Historical Funds'!B4:C3000,2)

The info in the Historical Funds sheet looks like this:
A B C
1
2
3
4 05/08/2005 59.11
5 04/08/2005 58.9
6 03/08/2005 58.91

The dates in Column B are UK formatted dates, without the time.

Does anyone know what I have to do to first format my VLOOKUP search
criteria correctly to handle dates? (maybe strip the time?) I know my
Vlookup works, because when I search on simple strings instead of dates
there is no problem getting my values from column C

Any help would be appreciated!

 

[Bob Phillips]

Dazman,

I think that you just want to use =TODAY()-1 instead of =NOW()-1

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Hi,

I have yesterdays date in a spreadsheet which is obtained by the
function =NOW()-1. It’s shown on the spreadsheet with a Date and a time
and I didn’t want the time so I formatted the cell to only show the
date.

I then want to refer to this Cell with yesterdays date, which is in B2
in my main spreadsheet with a VLOOKUP as follows:
=VLOOKUP(B2,'Historical Funds'!B4:C3000,2)

The info in the Historical Funds sheet looks like this:
A B C
1
2
3
4 05/08/2005 59.11
5 04/08/2005 58.9
6 03/08/2005 58.91

The dates in Column B are UK formatted dates, without the time.

Does anyone know what I have to do to first format my VLOOKUP search
criteria correctly to handle dates? (maybe strip the time?) I know my
Vlookup works, because when I search on simple strings instead of dates
there is no problem getting my values from column C

Any help would be appreciated!

1. If the date/time in B2 is a "real" Excel date; and if the dates in your
table are also real dates; then the formatting should not matter.

2. Again, given the above, the presence of the time should not matter either.
VLOOKUP should match to the date since that would be the largest value less
than or equal to the contents of B2 (Date + time).

The problem is elsewhere:

Looking at your data, it seems as if your dates are sorted DESCENDING. With
the VLOOKUP format you are using, dates need to be sorted ASCENDING.

Assuming that both B2 and the dates in your table are "real" Excel dates (i.e.
the underlying value is a serial number which is formatted to look like a date)
then several solutions are possible.

1. Sort your data so the date column is in ASCENDING order.
2. Use the FALSE argument for range_lookup; and also either
change your formula in B2 to =TODAY()-1 or, if you really need the
time, change the value in your VLOOKUP function to INT(B2) (that will remove
the time).

With solution 2, your formula would be either:

=VLOOKUP(B2,'Historical Funds'!B4:C3000,2,FALSE)

or, if you do not change NOW to TODAY in B2, then:

=VLOOKUP(INT(B2),'Historical Funds'!B4:C3000,2,FALSE)


--ron

 

[dazman]

Thanks Guys, that did the trick