Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Thanks.

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

1 Answer 1

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

http://support.microsoft.com/kb/180162

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.