Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with date formats
On Tue, 24 Jan 2006 07:45:03 -0800, "Enron"
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
importing csv files, problem with date formats | Excel Worksheet Functions | |||
Copy Color Formats Based On Column Date Values | Excel Discussion (Misc queries) | |||
Date formats | Excel Discussion (Misc queries) | |||
Date formats | Excel Discussion (Misc queries) |