Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using date problem
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. -- manse ------------------------------------------------------------------------ manse's Profile: http://www.excelforum.com/member.php...o&userid=22866 View this thread: http://www.excelforum.com/showthread...hreadid=502034 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using date problem
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? -- Kind regards, Niek Otten "manse" wrote in message ... 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. -- manse ------------------------------------------------------------------------ manse's Profile: http://www.excelforum.com/member.php...o&userid=22866 View this thread: http://www.excelforum.com/showthread...hreadid=502034 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using date problem
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. -- manse ------------------------------------------------------------------------ manse's Profile: http://www.excelforum.com/member.php...o&userid=22866 View this thread: http://www.excelforum.com/showthread...hreadid=502034 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP using date problem
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? -- Kind regards, Niek Otten "manse" wrote in message ... 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. -- manse ------------------------------------------------------------------------ manse's Profile: http://www.excelforum.com/member.php...o&userid=22866 View this thread: http://www.excelforum.com/showthread...hreadid=502034 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
search for latest date | Excel Worksheet Functions | |||
Ugh..another time and date problem...HELP | Excel Worksheet Functions | |||
date format problem | Excel Discussion (Misc queries) | |||
Excel 2002 date formulas problem | Excel Worksheet Functions |