Solved Excel Date Problem

January 23, 2010 at 02:59:02
Specs: Windows XP Pro
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!!

See More: Excel Date Problem

Report •


✔ Best Answer
January 28, 2010 at 06:07:50
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?



#1
January 23, 2010 at 03:09:22
is the date and time zone on you computer correct

caso1992


Report •

#2
January 23, 2010 at 04:11:40
Hi,

As Excel stores dates as numbers with 1 for 01 January 1900, this looks like some internal error in Excel. The '00' of January 1900 would be zero - a 'date' that Excel would never display.

Also showing an equals sign before a 'date' is clearly wrong.

When Excel has a date string entered, as you have done, it does an internal calculation and converts the date string to a number which it stores, but displays it as a date.

My feeling is that this is a corruption issue and you need to do a repair installation of Excel.

I would wait a while and see if there are any other suggestions.

Out of interest what does the cell display if formatted as a number and when formatted as Text.

Regards


Report •

#3
January 23, 2010 at 05:14:42
To Response 1 - Yes both the date and time zones settings are correct.

To Response 2 - I did try changing the format of the cells to both numbers and text and each time they behaved as expected.

I could enter the dates at text strings......but I just can't understand why they change to 00/01/1900 when the cells directly above are showing the correct dates.

I have several additional worksheets within this workbook and no others are displaying this problem!!

I think you may be right about a corruption issue with Excel!

I'll hang on a while longer to see if anyone else has some idea what's happening!!

Another funny thing is that after the "=" sign prefix the date is shown correctly on the formula bar.......but displays 00/01/1900 in the associated cell.......weird or what!!

Many thanks


Report •

Related Solutions

#4
January 23, 2010 at 05:58:40
Hi,

What happens if you enter this formula in another cell, =M8+40201 and format it as a date.
- where M8 is the cell showing 00/01/1900

Do you get today's date.

If so this confirms that Excel is storing your date as zero.

Can you also post what you see in the formula bar - click in the formula bar, select the whole formula and right-click and copy. Paste to the response, then select the cell, copy and paste it in your response.

Thanks


Report •

#5
January 23, 2010 at 07:35:16
Huney try formating the cell and keep it as a text format. I have tried what you are complaining about, it has been changed to normal date as I told you. Secondly one gentleman said about your computer date. Check that too..

http://www.testseek.com/labs/ ( All Reviews, Best Prices, One Site - Testseek.com)


Report •

#6
January 23, 2010 at 09:29:47
Hi Humar,

Using the formula "=C1974+40201" did give todays date - proving that Excel is storing the entered date as "0".

As I said before the formula line (for cell C1974) reads the current date but prefixed with an "=" sign i.e. "=23/1/2010"..........but returns "00/01/1900".

Any thoughts.....perhaps as you stated earlier a repair may be required?

Regards

--------------------------------
Hi Jesica,

I have tried formatting the cells as text and although this does work and shows the correct date it doesn't explain why this has just started happening from a certain point within an working spreadsheet.

As you can see by the cell refernce this is a large worksheet but I have other sheets within the same workbook (and of the same size) which are still working perfectly?

I've checked the time and date and both are correct.

Thanks for the suggestions.


Can you guys tell me if I can reply individually as I can't see a "Reply" option next to each response....only a global reply icon at the bottom of the screen? Sorry but I'm knew to this forum!


Report •

#7
January 23, 2010 at 09:57:26
Can you guys tell me if I can reply individually as I can't see a "Reply" option next to each response....only a global reply icon at the bottom of the screen?

That's so the rest of us can keep track of what's happening.

Just a couple of suggestions:

Try a Show Formulas in Cells using the CTRL key & the Tilde key: CTRL+~
and see if there is anything going on behind the scene.

Also you could try using the =CELL command.

Find a blank cell and do
=CELL("format",C1974)
and see what it returns, as well as maybe:
=CELL("contents",C1974)
=CELL("type",D3)

Might give a clue as to what's going on.

Have you checked on Macros & Add-ins to make sure they aren't the culprit?

MIKE

http://www.skeptic.com/


Report •

#8
January 23, 2010 at 11:02:55
Have you checked your Regional and Language Options Control Panel?

What is the format for Date and Time showing there?

If you want to send me the spreadsheet, I've sent a email address to you via PM. We can see what the spreadsheet shows on my system.

DerbyDad03
Office Forum Moderator


Report •

#9
January 23, 2010 at 14:21:13
Saving the file in SYLK format can remove some types of corruption.

Just save as a SYLK, with a different name,
then after you've saved it,
reopen in EXCEL and resave as an XLS.

MIKE

http://www.skeptic.com/


Report •

#10
January 24, 2010 at 04:14:28
Hi,

The reason the date is displaying as 00/01/1900 is because the integer part of the cell value is zero. This has nothing to do with regional or other date settings.

The integer part of the cell value is zero because it contains a formula that evaluates to less than one.

The formula is =23/1/2010
This is 23 divided by 1 then divided by 2,010
This equals 0.114428
If you format the cell as a time it will return 12:16AM, the time equivalent of 0.114428
The integer part of this number is zero.

Go into the formula bar and delete the "=" sign.

What happens to the text shown in the cell?

Regards


Report •

#11
January 24, 2010 at 06:02:35
Mike,

Tried the "cell" suggestions and got the following:

Find a blank cell and do
=CELL("format",C1974).........returned "D1"
and see what it returns, as well as maybe:
=CELL("contents",C1974).........returned "00/01/00"
=CELL("type",D3)........returned "I"

Does that tell you anything??

Not tried the "SYLK" suggestion yet but will give it a go this afternoon.
________________________________

DerbyDad,

This problem has only just started occurinig in this one worksheet within a workbook........all other sheets are bahaving as expected.

Date, Time and regional settings are all correct.
________________________________
Humar,

I've tried deleting the "=" but it simply re-instates itself when I hit return!!!


Thanks guys for all of the suggestions.


Report •

#12
January 24, 2010 at 06:55:55
Got me completely stumped now:

=CELL("format",C1974).........returned "D1"
This means the cell is formatted as: d-mmm-yy or dd-mmm-yy

=CELL("contents",C1974).........returned "00/01/00"
This returns the actual contents of the cell.

=CELL("type",D3)........returned "I"
This means the the type of entry in the cell is, I for text
The possible codes are: b for blank, l for text, v for value."

So you have conflicting codes being returned:

CELL("type") says it's a TEXT value
CELL("format") says it's a date format, which should show up as 24-Jan-10

TEXT and DATE formats don't mix.

MIKE

http://www.skeptic.com/


Report •

#13
January 24, 2010 at 08:05:24
Do you not want to send me the spreadsheet to look at?

Report •

#14
January 24, 2010 at 18:07:10
Hi,

If you delete the "=" and its automatically reinstated then it looks like a corruption.

The Cell values suggested by Mike show that the cell has a text entry as a 'value'
The text 00/01/00 is the value zero formatted as a date, but Cell says it is text and for a date it should return the value 0 and say that it is a value not text.
This still points me to an error in the code that recognizes entries in date formats as dates, and then stores them as values in Excel's date numbering system.

Can you enter text (not a date) into a cell without Excel putting an = before it in the formula bar?

If so it suggests a corruption in the code that handles date input.

Regards


Report •

#15
January 26, 2010 at 05:30:53
Humar,

Sorry for the delay in responding.....busy at work!

Yes I can enter the date as a text string without a problem.

Tried a repair on Excel with no joy!

I'm going to send the affected worksheet to "DerbyDad03" to see if he can make sense of it.
______________________

Mike,

Tried the ".SLYK" suggestion but without success!

Not 100% sure what you meant by saving it as a ".SYLK" file as this is not possible from within Excel......at least I don't think it is??

I simply changed the file extension of the saved document to a .SYLK extension, opened it in Excel (which was no problem) and then resaved as a ".XLS" file. Problem still there!

This is extremely annoying!!!!!


Report •

#16
January 26, 2010 at 06:18:42
To save it as a .SYLK file, on the Task Bar, File,
Save AS

In the drop box labeled:
Save as type:
scroll down to almost the bottom and it should give you the
.SYLK option

You could also try saving as an HTML, see here:

http://office.microsoft.com/en-us/e...

both .SYLK & HTML options are described.

MIKE

http://www.skeptic.com/


Report •

#17
January 26, 2010 at 09:30:41
Mike,

I did scroll down the file extension options but missed "SYLK".......duh!

Well I tried it on a copy of the worksheet........and it worked!!!!!!!!!!!

The only problem I had was that the worksheet formatting was lost so it will take a bit of time to re-instate that......but great...and thanks very much.

I've sent the sheet to "DerbyDad03" and I'm going to wait and see whether he has any ideas before I perform the "SLYK" option on the master worsheet .

Thanks again.

Regards,

MikeBr


Report •

#18
January 27, 2010 at 06:10:17
Thanks for sending the spreadsheet.

Go to Tools...Options....Transition tab.

Uncheck the boxes for:

Transition Formula Evaluation
Transition Formula Entry

QED


Report •

#19
January 28, 2010 at 04:14:23
Hi DerbyDad03,

You learn something new every day!

I guess in this case the date was being handled as a text string and evaluated as zero according to the Transition rules - hence the 00 Jan 1900 result when formatted as a date.

(Zero, formatted as a date in Excel will show the non-existent date 00 Jan 1900, as 'normal' behavior! )

... and just for interest enter 60 in a cell and format it as a date and you get another non-existent date - 29 Feb 1900. 1900 was not a leap year, so no 29 Feb.

Regards


Report •

#20
January 28, 2010 at 06:07:50
✔ Best Answer
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?


Report •

#21
January 28, 2010 at 08:02:41
DerbyDad03,

Many thanks for the solution........sent you an e-mail with some questions but you've answered them all above!!!

I have absolutely no idea how the "transition" settings were changed on that specific worksheet..........and like you I expected any settings would be applied globally to all sheets in the workbook!!

I also have no idea what these "transition" settings are used for!!

The workbook itself is used by me and A.N.Other.......who has "very" limited experience with Excel, therefore it may well have been finger problems!!!! These were masked by the fact that it only affectd the single worksheet!

Time to password protect the workbook I think.

Thank you all for your input.....and special thanks to DerbyDad03 for the quick fix.

This is a great forum.

Kind regards,

MikeBr


Report •


Ask Question