VLOOKUP using date problem

M

manse

Hi,

Thank you in advance if anyone has time to help me with this.

I use 'vlookup' to look up a table where

-Lookup_value is a date formatted as English (United States) Type
14-Mar-01 -

and the column in the

-Table_Array is also formatted as English (United States) Type
14-Mar-01 -

I am in the UK and I get the desired results from the look up but when
my colleague in the US tries, he gets results where the month and day
seem to be transposed - even though the cells have been formatted as US
dates.

Can anyone throw any light on this please.

Thanks.
 
N

Niek Otten

Hi Manse,

You don't format as UK or USA, you format as "dd-mmm-yyyy" or "dd-mm-yy" or
whatever.
But this is just formatting; the underlying value is what is used in the
VLOOPKUP (normally, but unfortunately you didn't give us your formula); that
is always the number of days since 1-1-1900 (roughly).
So the VLOOKUP should work.
There used to be an issue with formatting dates in older versions of Excel,
I don't know about recent versions: if the formatting happened to be the
standard date for your system, Excel just remembered "Standard date" and so
on the other system used the Standard date of *that* system, no matter what
you originally specified. But again, the VLOOKUP should work, UNLESS there
is a TIME part involved, which may be present, but not show.
What is the formula of your VLOOKUP? What are the input values? What if you
format them as General? What result did you expect and what did you get
instead?
 
M

manse

Thanks Niek,

It seems as though the problem does lie with the 'system.'

My regional settings in my Control Panel are English (United Kingdom)
and the spreadsheet was created on my computer using this setting. The
setting of my colleague is English (United States) so when his copy of
the spreadsheet sees 10/03/2003, it returns the values for the 3rd
October instead of 10th March.

For obvious reasons I am not keen to change my regional settings
'globally' on my computer but I wonder if there is a way to test this
(all the results point toward this being the problem).

Example:

I enter 8-Mar-02 and my US colleague gets results for 3-Aug-02

I enter 26-Jun-03 and my US colleague gets #VALUE! (as there is no 26th
month)

Since most people using this spreadsheet will be in the US, I would
then like to be able to re-create the spreadsheet in 'US format' so it
works correctly for them.

Many thanks again for your help with this.
 
N

Niek Otten

The international settings shouldn't be a problem; Excel stores dates in a
language-independent way.
Problems can occur when you manipulate dates in VBA, when you convert (back)
to text, or when you simulate dates using numbers or text, not using Excel's
built-in date features.
Can you give examples of your table, impute date, formula and results?
 
Top