So I've set up a data connection to a table on my website and the data imports just fine, but it formats the cells wrong.

Case in point:
The text coming in is "May 7". First Excel auto-converts this to "5/7/2011". But then it doesn't even display that, it displays "7-May".
What's even worse, if I change the cell formatting to "text" the date now becomes "40670". Basically, Excel refuses to display the ACTUAL freakin data I'm telling it to import! How frustrating is that?

I'm just flabbergasted. Not only does Excel never show the ACTUAL text I'm importing, it converts it in THREE different ways! WTH? They think this is helpful?

Anyway, every time I import the table, the formatting changes, so it doesn't STAY as text. And yes I know I can put a single quote and type " 'May 7 " and it will stay, but this needs to be automatic. I'm not going to spend 10 minutes re-typing every cell that displays wrong, and it all goes away when I refresh the data anyway.

So the question is, how can I force the cells to be text only, and not reset when data is imported? Or basically, get Excel to show me the real data I want, and not it's dumb conversions. It has to be a solution that is unique to the one file, not a global change, since other people use this file, the settings have to be in the file.

Thanks!

Format you cell as date and the select the date option you would like to see it in

Hi lightning hawk and welcome back to daniweb. Do take note of the date the thread was created and try not to revive all dead threads you see. Hope you understand.:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.