I am using a INDEX, MATCH function in sheet Car Search to pull data from sheet Raw Data. One of the cells is Date Purchased, this will show the date in format dd/mm/yyyy (UK Format).

The problem is, when I enter a date in this format, it doesn't correctly pull over to the Car Search sheet. Instead, it shows up 42350 when I entered 12/12/2015 in the Raw Data sheet. Both cells have been formatted to Date, but it is still showing up as 42350 and not 12/12/2015.

Is it because the function is preventing it from showing date format? Anybody know why Excel does this? I have tried on a new sheet, but same result.

Please see images below.

Car Search Sheet Raw Data Sheet

  • 2
    42350 is the difference in days between 12/12/2015 and 1/1/1900 (off by one due to a deliberately introduced error counting 1900 as a leap year). That is how Excel stores dates. So all you need to do is format that cell to show a date. – Ron Rosenfeld Aug 16 '16 at 1:58

You can simply change the cell's format to 'Date' and it will be correct.

When you type a function in cell, Excel adjusts the format of the cell according to the function. This is often correct and useful, but sometimes not.

Here, Excel thinks that the result of the INDEX function is a number, and changes the cell format to 'Number' whenever you type or change the formula. So you might have to repeat the 'set format to date' when you edit the formula (not the input values, the formula itself). However, once the formula is correct, that would not be needed anymore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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