Excel Date Problem

[jvinsky]

The problem occurs when I attempt to insert a lookup function in one spreadsheet which looks up a raw material and returns the date of the last receipt of that raw material. The date returned to the spreadsheet where the function resides ends up being one day later than the correct date and the year reverts back to "00". For example the date 12/16/04 will lookup and return a value of 12/17/00. Could this possible be due to Leap Year and Excel's difficulty in dealing this change? Any help would be appreciated.

Joseph

 

[macleanb]

Probably due to two reasons:

1. vlookup has a parameter on the end that defines whether its looking for an exact match or not (see the help on the function). If you are not using the exact match version of the call then it has rules about when/what to return (and it expects that the list is sorted)

Also this is complicated by the fact that dates are really numbers (usually - but not always) sometimes they get input as text (various causes). You can tell wehter the date is input correctly by formatting the block, if they all change format, then they are all input correctly.