Nimbus

A temp was messing with some ones spread sheet and has changed all the date formats. At the moment it shows the correct date in the format bar, but not in the cell. See below.




I need the cell to show the date but it wont!

I've tried everything I can think of (which is not a lot ) to fix it. Changed the cell format etc but can not get it to work.

Can anyone help?

druddle

Looks like the cell format has been changed to "Special" - "Zip Code".

Will keep looking at this.....

Dave

Nimbus

I've changed to format to date but it still shows this number instead of the date. No formating I apply changes how it looks in the cell. But the formular bar does change, for example, if I change it to another format.

druddle

check to see if any protection has been applied to the sheet or workbook <Tools><Protection> and then if theres any protection on the cells <Format Cells><Protection>.

Still scratching my head - this is an awkward on !!

Dave

fast bloke

I've seen this before. I can't remember how to fix it, but it will come to me. The number you see is the number of days after the earliest date the PC can recognise. Try copying the entire column into a fresh column. I'll keep thinking

Mo

Nimbus,

try changing the format to custom, dd/mm/yy

let me know if it works,

Zak.

Nimbus

Druddle,

There's no protection. Already checked


FB,

When I copy the data into a fresh sheet, it does show the dates


Mo,
Tried that. Still shows the number instead of the date.


Thanks for the input so far guys..

ChrisB

Fast Bloke looks to be on the right track.

37120 is Excel's way of showing 17/08/01 as a integer. IIRC, 01/01/1900 is 1.

fast bloke

Nimbus - mail me a bit of the file. Cut out names etc if it is confidential. I know there is an easy fix, but I can't replicate the problem to try it out

Nimbus

LOL, Nice one FB. Thanks again.

Nimbus

FB,

E-mail on it's way

Thanks

fast bloke

Nimbus - There is a bug in the spreadsheet. To fix, do the following

Create a new sheet. On the ASF sheet, Click the top left row/column selector. (Above row 1 and left of column a.) Click copy. Go to the new sheet. Click the same top left button. Click paste. The dates should now be in the correct format. The colum widths will be a bit screwed up, but easily fixed. Let us know if this works.

[This message has been edited by fast bloke (edited 20 September 2001).]

Nimbus

Thanks FB,

I suppose I should have tried this myself

Any idea what the bug was?

[This message has been edited by Nimbus (edited 20 September 2001).]

fast bloke

No idea. The page was behaving as if it was locked and protected. Unlocking it had no effect, but should allow you to make changes. You can't put anything else into it either. Try putting in cruuency with a puond sign or something, so it is not only the date format which is screwed. As the man from Microsoft said - they all do that sir. You could probably find the bug if you wanted to spend 6 weeks looking for it. Only problem is you would need to single it out from the other 2 million bugs Excel already makes use of.

MattN

how was the data put in to the spreadsheet?

sometimes when importing data, whatever the data was imported as i.e. text it can't be changed. It can be a real pain.

Matt

Nimbus

Matt,

Originally it was typed in as a date, but then a temp changed the format but we are not sure what format she used (she has since left).

fast bloke

Matt -
I don't think it was something the temp did. Usually if a person causes the problem it can be fixed. Looked to me like the thing was simply ignoring any formatting changes you tried to make and could not understand all its own formatting. Even creating new rows and putting in fresh data didn't work

MattN

a good trick to try, try recording a macro of you changing the cell format. then have a look at it in the VB editor and see what it says.

The good thing is you can use the macro on a new spreadsheet to see if the original is knackered!!!

If the data was simply keyed in I don't see how it could lock the cell format, unless of cause there was indeed a bug or a virus.

Microsoft, gotta love em!