Excel 2007 - Cells converting to DATE format on their own

[ladyck3]

I am curious.... I have a workbook that I use for filtering data on a daily basis, plopping data into it, my vlookup pulls the records I need and I delete the rest, then to another page... this is done 4 times... each day.

Today (as has happened in the past) my lookup was not working and for the life of me I couldn't figure it out, then it dawned on me.... the formatting which was GENERAL had somehow changed to DATE on the entire workbook...

Corruption I'm thinking is the cause but how do I fix this other than to change it when it does it or how can I stave off this mishap from happening again?

This has happened often not only on this file but in other things as well... so I'm curious if this has happened to others and what is the general concensus regarding this anomaly.

Thanks in advance....

ladyck3
aka: Laurie

 

[SkipVought]

Laurie,

If ONLY what happened is that your FORMAT changed, then it makes NO DIFFERENCE AT ALL! A Real Date is just a NUMBER.

So it makes no difference of your search value is 41033 and you have 41033 in your lookup range or you FORMAT the 41033 to display 5/4/2012 (Happy Star Wars Day) You WILL get a match EITHER WAY! You can even SWITCH the formatting to the search value -- NO DIFFERENCE!!!

Please be more specific about your serach value and your search range. What did not work under what conditions and what did you do to CORRECT the issue?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

The Vlookup in all cases is looking up data... text data.
Strange thing is... well ok...
COL A in each sheet is an =cellreference
I pull the column I want to lookup to the first column and do the formula in B.

Trouble is... I can tell when this anomaly has occurred because Column A ... when the sheet is empty should show 0
but instead it shows Jan-00-00
All of the fields whether number or text all are formatted to the DATE format. If you highlight anything and look on the ribbon for the format, all data is "DATE" So I have to change those which are number back to number, those which are text back to General... its a HUGE report... when it doesn't work, its because it has had a mind of its own and changed them all to Date.

I have a monthly report that I do as well which is expansive and all I do is remove the previous month's data and paste in the new data but sometimes the workbook in its entirity has changed to DATE format. I've done NOTHING to the formats... its just frustrating.

I can fix it and carry on but its frustrating to have to do this and if I can figure the cause maybe I can prevent this from happening again. That's all.

Thanks Skip!

ladyck3
aka: Laurie

 

[SkipVought]

The Vlookup in all cases is looking up data... text data.

Trouble is... I can tell when this anomaly has occurred because Column A ... when the sheet is empty should show 0
but instead it shows Jan-00-00

????
0 and Jan-00-00 are IDENTICAL VALUES

AND

0 and Jan-00-00 are NOT TEXT!!!

So from what you have stated, I cannot see why your lookups would fail to work. You appear NOT to be looking up TEXT!

I have no answer for why the FORMAT would change, but as I previously implied...

Changing the FORMAT changes NOTHING!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok... well then I have no idea what is going on... but when I change it all to General it works again.

<shrug>

I totally understand what you are saying... I'm just telling you what I see.. when my numbers or whatever data change to Jan-00-00 my worksheets get screwed up and I have to reformat it all...

Let's not even get as far as the vlookups, why... do the cells change to that date format whether number or what? What is the trigger? Forget formulas, the data itself just changes on its own... the FACT that, when I look at it, and I see Jan-00-00 I have to change the formats to get the right information.

This is for numbers that should be numbers not dates... the format changes to dates sporadically.. the cause is what I'm after...

Maybe its such an anomaly that its only me... that is not a surprise to me... no worries.

ladyck3
aka: Laurie