vlookup with date formats

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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