Number changing to date

[hgbegdhegdh]

I have this age old problem in many excel files

When I open excel file, numbers gets change to date.

This does not happens everyday but happens sometimes on some day, in some sheets in some rows. But whenever it happens it spoils by half day.

I tried lots of solution, like changing cells formats to number and general, but it works for few days and then again same problem happens

In some cells there are formulas, and here I changed by multiplying with 1, but this is time consuming and also problem happens in this solution also.

I am using Windows 10, Office 10, 64 bit

Please provide some fullproof solution to this

 

[James006]

Hello,

Could you post a couple of examples ... since for 'numbers' to be changed into dates ... is quite a specific issue ...

 

[Yongle]

How are values entered in the workbook ?
- are ANY values updated automatically from another source
- does workbook contain any VBA ?
- does the problem always happen in the same column ?

 

[hgbegdhegdh]

1. Values are either copy-pasted / entered manually / derives from other sheets / files through formula. Mostly it creates problem when formulas are there in file
2. Yes contains macros
3. I have not noticed regarding have issue in same column or not, but 99% in same column

Example - 480.4 changes to 24-Apr-01 when i open file

 

[RoryA]

There is no Office 10 - do you mean 2010?

This is quite a longstanding bug with no defined cause or cure, that I am aware of. Do you have pivot tables in the workbook?

 

[hgbegdhegdh]

There is no Office 10 - do you mean 2010?

This is quite a longstanding bug with no defined cause or cure, that I am aware of. Do you have pivot tables in the workbook?

Yes, 2010

I am not using pivot tables.

Is there any latest version above 2010 is available where this bug is fixed ?

 

[Peter_SSs]

Although this question was about Excel 2007, I suggest you have a look at Ron Coderre's response and see if what he suggests there fixes your problem.

 

[hgbegdhegdh]

Although this question was about Excel 2007, I suggest you have a look at Ron Coderre's response and see if what he suggests there fixes your problem.

Ok, will surely try this

 

[hgbegdhegdh]

First of all thank you to all who has answered and tried to help

I read below solution on 1 website and this seems to solve the problem fast when cells are changed to date. This may not help in preventing error in future, but if error happens this would be quick and easy way to return back to normal

On any sheet, click into a cell. Click "Format Cells" on the Home tab, then "Custom". Look for any format types that contain: [$-409]m/d/yy h:mm AM/PM;@ Delete them, and VOILA! Every non-formatted cell on every sheet will be fixed, without affecting cells that you've specifically formatted otherwise!!! You may need to unprotect all of your sheets first if they are password protected.