+ Reply to Thread
Results 1 to 4 of 4

Vlookup and date format problem

  1. #1
    Registered User
    Join Date
    08-04-2005
    Posts
    27

    Unhappy Vlookup and date format problem

    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!

  2. #2
    Bob Phillips
    Guest

    Re: Vlookup and date format problem

    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)


    "dazman" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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!
    >
    >
    > --
    > dazman
    > ------------------------------------------------------------------------
    > dazman's Profile:

    http://www.excelforum.com/member.php...o&userid=25903
    > View this thread: http://www.excelforum.com/showthread...hreadid=393542
    >




  3. #3
    Ron Rosenfeld
    Guest

    Re: Vlookup and date format problem

    On Sat, 6 Aug 2005 06:49:13 -0500, dazman
    <[email protected]> wrote:

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

  4. #4
    Registered User
    Join Date
    08-04-2005
    Posts
    27

    Cool Thanks Guys, that did the trick!

    Thanks Guys, that did the trick!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1