Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm fairly proficient in using vlookups, but I'm having an issue vlooking up dates between two different spreadsheets.

=VLOOKUP(B6,'[example.xlsx]Sheet1'!$B$1:$AA$260, 19, FALSE)

My formula is retrieving a date fine, but the date is different when the cell is formatted for a date. Yet, when I change the formatting on each spreadsheet to display the date as a number, the number is the same (40115). The dates are displaying differently in each spreadsheet and I can't figure out why, they differ by about 3 years and 1 day (10/30/13 vs. 10/29/09). One was previously .xls, but I saved both a .xlsx.


share|improve this question
I added 1462 to each number after VLOOKUP which resolved the issue, but I'm interested in knowing the root cause of this issue as I know others who have faced it. – Mitch Jun 27 '12 at 14:50
up vote 4 down vote accepted

the problem is from the 1900/1904 date systems that Excel supports. Excel for mac defaults to 1/1/1904 as its date zero, while Excel for windows defaults to 1/1/1900 as its date Zero

The difference is 4 years, or, as you have found out, 1462 days

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .