Date-Formatting changed and I don't know why!

[simonheller]

All of a sudden last week the formatting for my Date column stopped working the way it had since I started the spreadsheet almost a year ago.

Until last week dates had been being displayed: March 8, 2011. I had been entering the date with numbers separated by forward slashes, and Excel would use the first number as the month, the second as the day, and the third as the year (3/8/11 came out as March 8, 2011). Now all of a sudden 3/8/11 is coming out as September 11, 2003.

I have verified that the same formatting is being applied to all the cells. It is. If I delete the properly displayed date in one of the pre-"September 11, 2003" cells, whatever I now type will come out wrong.

Could this have anything to do with another sudden change in the way info is displayed? Before, when I clicked and dragged the vertical axis scroller at the right edge of the screen, the view would not change until the mouse was unclicked, but the row numbers would be shown as I dragged (super-fast in a very small box to the left of the thing that gets dragged). Now the numbers no longer appear, but the rows fly by super fast as I drag (before unclicking).

Unfortunately I cannot include the spreadsheet in this posting because it way too large (Columns A to FQ and over 500 rows, plus lots of formulae and conditional formatting.

If anyone has any advice on how to troubleshoot this extremely frustrating problem I will be very grateful.

--Simon

[teylyn]

could you possibly post a screenshot with the cell highlighted, so its value is visible in the formula bar, and also showing the formatted cell? It seems that your system's date settings have changed.

Are you sure that 3/8/11 comes out as September? If the value is interpreted as year/month/day, it should be August.

You don't need to post the whole spreadsheet. Try and re-create the problem in another file and post that.

[simonheller]

Oops... you`re right. 3/8/11 is coming out as August 11, 2003, not September.

I have tried and tried and tried to pare down the size of the file in order to be able to attach it here, but I can`t get it below about 1.40MB (from it`s normal size of about 2.50M) no matter how many columns and rows I delete. I even removed the formulas and conditional formatting from all cells. No luck.

But it is just as I explained: One day 3/8/11 was coming out as March 8, 2011, and the next day it was coming out as August 11, 2003.

In the screen shot I have attached, I entered the last 3 dates as 3/8/11, 3/9/11 and 3/10/11. I don't know what it used to say in the formula bar before the formatting started behaving differently, but I am certain that the previous 3 dates were entered as 3/7/11, 3/8/11 and 3/8/11. When Row 589 of Colunm A is highlighted the formula bar reads: 2011-03-08. I have verified that the same number formatting applies to all the cells in the column (Catergory: Date; Type: English Canada, 2nd to last choice, with month spelled out, followed by day, then 4-digit year).

Our IT office assured me that no changes to settings in Windows or anything like that have ben made.

[teylyn]

OK, can you copy a few rows and paste them into a blank workbook? What do you see then?

What do you see in the formula bar when you select a cell in column Q?

Can you make a copy of the workbook, delete all but 10 or so rows of data and post that? You can remove names and confidential data.

[simonheller]

I managed to attach the file to this post. Most of the formulas (mostly used for calculations based on the data you will see) and all personal info have been removed.

[simonheller]

PS. I don`t know what version of Excel was on my network before these problems started, but right now it is Excel 2003. If the Excel version has, in fact, been changed recently that would explain the change in the vertical scrolling I mentioned. Could the same thing be responsible for the way Excel is interpretting dates that I am inputing the same way I always have in this spreadsheet? Tomrrow morning I will verify with my IT office whether the version of Excel on my network has been changed.

[teylyn]

Hmm, when I open your file, I see these values in cells A2 to A10

March 30, 2010
March 28, 2010
March 28, 2010
March 27, 2010
March 27, 2010
March 28, 2010
April 1, 2010
March 28, 2010
March 23, 2010

I guess it's something to do with your computer settings. I'll call in the cavalry.

Stand by.

[romperstomper]

What are the Regional Settings on your PC set to? (especially the date formats)