• 0

Solved Excel Date Problem

  • 0

All of a sudden I have a problem with entering dates in a spreadsheet which I’ve used for years!!
The cells in my date column are formatted as dates (surprise, surprise) but now when I enter a date e.g. 23/01/10 the cell changes to 00/01/1900 and the date is preceded by the “=” sign??
I have tried reformatting to a custom date and also using format painter from a correct date cell – same thing happens??
Am I missing something obvious?? HELP!!


1 Answer

  1. re: “You learn something new every day!

    Me too!

    The way I “solved” this problem was by entering dates in other cells throughout the spreadsheet and they all did the same thing.

    I then tried some other things, such as 45*12 and Excel put the equal sign in front of the string and evaluated it. I tried that in “clean” spread sheet and it retained the 45*12.

    At that point I didn’t think it was “date corruption” problem but possibly a setting, so I started comparing options between the 2 sheets and found the Transition settings were different.

    I’m just glad that Excel doesn’t retain that setting for the entire Excel “instance” like it does with the Manual Calculation setting. As you may know, if the first workbook that is opened has Calculation set to Manual, all subsequently opened workbooks will also be set to Manual.

    If that had happened with the Transition settings, I may never have found the problem, since that is not a setting I often (ever!) deal with and may not have realized that they shouldn’t have been selected.

    The next question is why were they selected in the OP’s sheet? In his email he said “As you will see the date is fine up to cell C1942 but then changes to this “formula state.”

    The question is: Why were those setting changed?

    • 0