cell's displaying 0.78125 instead of 6:45 PM

[Thomas]

We have various people entering data into a spreadsheet
and somehow one of them altered one sheet of three where
the above is now the case. Formula bar shows 6:45 PM but
the cell shows 0.78125. Re-formatting does not alter this.
Only solution thus far is to enter '6:45 PM with the
preceeding tick mark. Cell then displays 6:45 PM. What got
turned on and how do I turn it off to get back to normal
data entry with ' preceding numeric entries?

Thomas
Strathroy Foods

 

[Jerry W. Lewis]

0.78125 is 6:45 PM with the format changed to General.

If you format it as time and it still looks like 0.78125, then it is the
text string "0.78125" instead of the number 0.78125. Note that
prefixing with a tick mark gives you the text string "6:45 PM" instead
of the time value 6:45 PM.

Formats change the display of the value, but not the value itself, in
particular, changing the format does not change the value from text to
numeric or visa versa. If you format the cell that contains the text
"0.78125" as numeric and then re-enter the value, then you will get a
numeric 0.78125. Alternately copy an empty cell, select the cell
containing the text "0.78125" and Edit|Paste Special|Add to coerce the
value from text to numeric.

Jerry

 

[Jon Barchenger[MS]]

Good afternoon Thomas -

I would suspect that the formated the cells as text before entering data into them.

This will retain the value in the cell as it is typed in. If you then format the cell as TIME the number in the cell doesn't change, but, it will appear
as a time value in the formula bar.

To correct this you need to do an EDIT - CLEAR - FORMATS on the target cells.
Any cells that current contain data - the data will need o be re-entered.
However, all new entries will FORMAT as TIME and display correctly.


Please let me kow if you need further help with this issue.

Thanks,
Jon Barchenger
--------------------
**Content-Class: urn:content-classes:message
**From: "Thomas" <[email protected]>
**Sender: "Thomas" <[email protected]>
**Subject: cell's displaying 0.78125 instead of 6:45 PM
**Date: Mon, 1 Dec 2003 07:46:49 -0800
**Lines: 11
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: 7bit
**X-Newsreader: Microsoft CDO for Windows 2000
**X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**thread-index: AcO4IlT8Y1QhiUPUQ2+iBnCjG+EQSQ==
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa06.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:174537
**NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**We have various people entering data into a spreadsheet
**and somehow one of them altered one sheet of three where
**the above is now the case. Formula bar shows 6:45 PM but
**the cell shows 0.78125. Re-formatting does not alter this.
**Only solution thus far is to enter '6:45 PM with the
**preceeding tick mark. Cell then displays 6:45 PM. What got
**turned on and how do I turn it off to get back to normal
**data entry with ' preceding numeric entries?
**
**Thomas
**Strathroy Foods
**