+ Reply to Thread
Results 1 to 3 of 3

vlookup with date formats

  1. #1
    Enron
    Guest

    vlookup with date formats

    Hi, I put a vlookup formula searching for a data corresponding to a date in a
    table. It is only working with a european date format (dd/mm/yyyy) although
    my regional settings are set to US. Once I change (on both tables) the date,
    the vlookup doesn't work anymore ... really strange

    Did someone already see that ?

    Thanks


  2. #2
    Ron Rosenfeld
    Guest

    Re: vlookup with date formats

    On Tue, 24 Jan 2006 07:45:03 -0800, "Enron" <[email protected]>
    wrote:

    >Hi, I put a vlookup formula searching for a data corresponding to a date in a
    >table. It is only working with a european date format (dd/mm/yyyy) although
    >my regional settings are set to US. Once I change (on both tables) the date,
    >the vlookup doesn't work anymore ... really strange
    >
    >Did someone already see that ?
    >
    >Thanks


    Excel stores dates as numbers. Frequently, the kind of problem you report is
    due to some or all of the dates being stored as text strings rather than
    numbers.

    VLOOKUP will only work if all the dates are stored as numbers (or proper dates)
    or if all the dates are stored as text strings.

    If you select a cell with a date, and cannot change its format, then it is
    likely a text string.

    You can also check by =ISTEXT(cell_ref) For proper dates, this should return
    FALSE.

    I would suggest getting all your dates into the proper date format.
    --ron

  3. #3
    Enron
    Guest

    Re: vlookup with date formats

    Hi, you 're right those were stored as text. The istext formula gave a "true"
    result. However, when I went in the "Cell Format" menu, the cell was
    considered as a date ...

    In fact I've applied a date format, but as the data were in a pivot table at
    that moment it was apparetly useless.

    Anyway, thanks to you, now it works fine !

    Many thanks

    "Ron Rosenfeld" wrote:

    > On Tue, 24 Jan 2006 07:45:03 -0800, "Enron" <[email protected]>
    > wrote:
    >
    > >Hi, I put a vlookup formula searching for a data corresponding to a date in a
    > >table. It is only working with a european date format (dd/mm/yyyy) although
    > >my regional settings are set to US. Once I change (on both tables) the date,
    > >the vlookup doesn't work anymore ... really strange
    > >
    > >Did someone already see that ?
    > >
    > >Thanks

    >
    > Excel stores dates as numbers. Frequently, the kind of problem you report is
    > due to some or all of the dates being stored as text strings rather than
    > numbers.
    >
    > VLOOKUP will only work if all the dates are stored as numbers (or proper dates)
    > or if all the dates are stored as text strings.
    >
    > If you select a cell with a date, and cannot change its format, then it is
    > likely a text string.
    >
    > You can also check by =ISTEXT(cell_ref) For proper dates, this should return
    > FALSE.
    >
    > I would suggest getting all your dates into the proper date format.
    > --ron
    >


+ 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