Corrupt Excel?

  rezeeg 14:56 22 Nov 2006
Locked

Where's VoG™ when I need him? ;-((

I've recently changed my spreadsheet from Works (where I had no problems, why did I change!!!) to Excel 2003 and I'm having problems with the formatting of some cells eg:

S/S 1 - One column formatted as hh:mm but some cells don't work correctly, eg enter 26:53 and it displays in the cell as 02:53 and when highlighted my entry now displays as 01/01/1900 02:53:00. Formatting the cell individually doesn't change things. (btw cells above and below diplay OK).

S/S 2 - One column formatted as 'general' but an entry '2-1' becomes 02-Jan in the cell and when highlighted my entry now displays 02/01/2006. Again, formatting the cell individually doesn't change things.

In S/S 1 the cell remains formatted as hh:mm, but in S/S 2 the cell format changes back to dd-mmm.

I've tried 'Detect and Repair' to no avail.

I hope somebody can make sense of this and can tell me where I'm going wrong. Thanks

  rezeeg 14:59 22 Nov 2006

ps should have added that if I cut and paste from a cell above the entry and formatting look OK, but if I then overwrite with the correct data it all goes wrong again, eg

copy 1-0 into cell - all OK; overwrite 2-1 and 02-Jan reappears.

  GroupFC 15:20 22 Nov 2006

I'm not VoG™ but - the 26:53 doesn't display as you wont it because IIRC Excel works on a 24hour basis, hence 02:53. As to how to get it to display as you wish you might have to wait for VoG™ !

Is the entry 2-1 actually a date? If you format the cell as text does it display as you wish?

  wee eddie 15:21 22 Nov 2006

My day is 24 hours long. - So - 26.55 is 02.53 tomorrow!

Well of course it does. What do you think the "-" sign does!

I am not being facetious but you will have to think carefully how you enter your cell values.

  rezeeg 15:45 22 Nov 2006

GroupFC 2-1 is a score - I have statistics for my local football team.

Just tried formatting with 'text' and it now seems to be OK, thanks. (The other 'good' cells are still formatted as 'general' however!).

wee eddie, doh - yes the brain cells are degenerating ;-( I originally had the column formatted as mm:ss (changed to hh:mm out of desperation lol.

I've now changed it back to mm:ss and my entry of 26:53 displays in the cell as 53:00 and my entry changes to 01/01/1900 02:53:00.

  rezeeg 15:51 22 Nov 2006

wee eddie in my defence, I thought the hh:mm indicated a time span not a 24 hour clock.

  GroupFC 16:01 22 Nov 2006

"I've now changed it back to mm:ss and my entry of 26:53 displays in the cell as 53:00.... " - it will do I think because it is reading the 26 as hours (and ignoring it because you have formatted the cells as mm:ss). Try entering the data as 00:26:53 which will give you the minutes and seconds value.

Once you have established the right format in a particular cell to get the same format in other cells try this:- Right click the correct cell and select copy, select the destination cell(s) and then right click and select paste special and then select formats in the dialogue box and then OK (and Bob (should be!)your mother's brother!).

HTH.

  exdragon 16:01 22 Nov 2006

If you scroll down the Custom catergory and choose [h]:mm:ss then knock off the :ss, it should show what you want.

I think!

Enter VoG™, stage left, to put you right!

  Simsy 16:04 22 Nov 2006

26:53 displayed as such, you'll have to format it as [hh:mm]

This forces the hours to show as a DURATION not as a TIME

There is no such TIME as 26 o'clock, but there is a DURATION of 26 hours.


Does this help?

Regards,

Simsy

  exdragon 16:06 22 Nov 2006

If you want the same format in the same column on each s/s, click on the first s/s tab, hold down control then click on all the other tabs. Then select the colum(s) you want to format.

Just don't forget to hold down shift and click on the active tab when you've finished.

Apologies if this is stuff you new already!

  exdragon 16:07 22 Nov 2006

Knew, even!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?