Thread: MMM JJ Date Format Issues with different countries

Hello Everybody,

I am new here and usually wouldn't bother a forum with my mundane questions, but this one has me baffled.

I am having a simple Formula which reads out the MONTH() and YEAR() out of a cell that is formatted with MMM JJ and adds this to a number (which itself is a VLOOKUP on another cell, but that has little to do with my problem).

Now I am getting a file from Hungary and when I open it (in Germany), the date in the cell is not in MMM JJ Format, but a normal date (i.e. 01.01.2012 instead of Jan 12) and the formula doesn't read out month and year, but simply states #Value!

However, as soon as I enter 01.01.2012 (I simply retype what the person has already typed) myself or even just go into the cell to edit the formula and then hit enter, it works. And yes, my calculation is on auto, not manual update.

I am specifically mentioning the countries because I thought it could have something to do with the date format of the different countries, but I am not sure about that, since both are European countries (same date format) and MMM JJ should be country neutral. Maybe ultimately the error lies in how Hungary enters the data in the first place, but I can't control this.

Any ideas would be appreciated.

Thank you.

Alaaf!

Could it be that the cells just LOOK like dates, but are really text? Do the test: format the cells with the "dates" with General. Do they change? If not, they're text. You might be able to change them into real dates with the DateValue() function (whatever that is called in German).

This is a rare case where I'd like to see a screenshot rather than a workbook. Could you post one?

Und grüß mir den Dom!
♪♫ Wenn isch su aan ming Heimat denke, un sinn dr Dom so vüür mer stonn ....♫♪

cheers,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save