1

Excel 2010 sometimes opens with formulas or number entries changed to dates in many columns throughout workbook. But not all columns.

The "number format" automatically changes from GENERAL to DATE. This effects dozens of columns. It does not happen during use while excel is open. It only occurs after the document is closed and reopened. It has occurred about 4 times in the past 2 years. But if it was not for my backups, this would be a mess.

It has happened with different files.

The file effected today has 22 sheets with 15 sheets effected. The effected cells are manifested with hash marks because the dates are too long to fit into the narrow cell.

I am inspecting the damaged file and just realized that ALL sheets have ALL cells converted to DATE number format! That ought to simplify the diagnosis?

Finally, is there a simple way to reversing this problem other than opening an older version of the file?

  • 1
    Your post is confusing... You say only some of the cells are changed, and then later all cells change. Which is it? – Dave Jun 8 '15 at 7:35
  • 1
    What file type is this? Is it .XLSM? – Dave Jun 8 '15 at 7:39
  • There are a few other posts on the internet explaining this, can I assume you have seen some posts? Whilst your question describes the issues, it doesn't show what you've tried (which may help narrow down the issue) :) – Dave Jun 8 '15 at 7:43
  • I have also edited your post for you. You originally had just 1 big block of text, which is actually quite hard to read. If you lay your question out similar to what I have done (in the future) you'll hopefully find more people will help you :) – Dave Jun 8 '15 at 7:49
  • Yes .XLSM; All cells in all sheets automatically changed from whatever I set, to DATE. – Tom X Jun 8 '15 at 8:02
0

You mention in your comments that your file is an .XLSM. This means, as I'm sure you know, it runs Macro's (code).

Whilst technically malicious code (like a virus) could be causing the issue, I don't believe it is, more likely, it's code you have which is buggy or doing something when you don't expect it.

Therefore, you'll need to open the VBa screen (Developer tab) and see what can be disabled and or removed and or fixed etc

But before you do let's just disable it; in the developer tab, you click on Macro Security and for now, change it to disable all macros with notification , then in any cell you choose, add some text, save, and close the file. Reopen the file and do not allow the macro's to run and see if the cell has changed or not, thus proving if the issue is with a macro

  • I understand your approach but the only problem is that this problem with the change in number format is intermittent. I cannot duplicate it. And it occurs very infrequently; about once every 6 months. There is a macro that I downloaded a few years ago that converts a number into text; 121...One Hundred and Twenty One – Tom X Jun 8 '15 at 8:19
  • 1
    OK, well listen @TomX, this is a Q&A site, not a forum so it's totally cool to edit your original post (the question). I suggest you edit your question, and add that this file runs macros, explain how often the issue occurs and where you got the macro from. It may help others but as it only occurs once every 6 months, I think the question is too broad for us to answer :( – Dave Jun 8 '15 at 8:42
  • @TomX: I agree with Dave. Unless this was a known problem with an easy fix, any solutions people might suggest here unfortunately cannot even be confirmed by you to have helped within a reasonable time-frame given how intermittent this is and how you cannot replicate it. Thus far the macro hypothesis seems most probable, unless you're triggering some sort of bug in Excel (although it that were the case there'd be lots more similar reports). – Karan Jun 8 '15 at 8:47

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.