Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
strange about ":"
Dear all,
just now I input 1:198 in one cell of my worksheet, it shows me 0.17916(6 is cycling). I feel strange, cause I havn't input "=" befor it. and I don't know what kind of operation Excel does to me. try with 1:60, result is 0.833333333. and I found when the number in position of 198 is large than 59(from 60), it will does this strange thing. if the number is smaller than and equal to 59, the data will be the time. could anyone give me an explanation that what excel does to me? thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
strange about ":"
Hi
The problem is Excel is trying to be helpful!! Entries with a colon are assumed to be Time. Thus, 1:59 would be interpreted as 1 hour 59 minutes. Clearly one cannot have 1 hour 60 minutes (1:60), as that would be 2 hours Excel stores it's times as fractions of a day (24 hours) so the result (which is 0.08333 not 0.8333), and it is changing the cell format to General. If you reformatted the cell as hh:mm you would see 2:00 If you multiply 24 * 0.08333 you get 2 (representing 2 hours) Similarly 1:198 is taken as 1 hour 198 minutes, which gets changed to 4 hours 38 minutes, which in General form is 0.179167. If you do want to enter 1:198 as a ratio, precede your entry with a single quote, which will force it to be treated as Text, or pre-format the cell as a Text cell before carrying out data entry. -- Regards Roger Govier "ViestaWu" wrote in message ... Dear all, just now I input 1:198 in one cell of my worksheet, it shows me 0.17916(6 is cycling). I feel strange, cause I havn't input "=" befor it. and I don't know what kind of operation Excel does to me. try with 1:60, result is 0.833333333. and I found when the number in position of 198 is large than 59(from 60), it will does this strange thing. if the number is smaller than and equal to 59, the data will be the time. could anyone give me an explanation that what excel does to me? thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
strange about ":"
Hi,
The colon (:) tells excel that the input is in time format. As your cell is formatted to general it is showing as 0.17916666667. If you format it as time it will show as 4:18 which is 1 hour and 198 minutes. The same with 1:60 which is 1 hour and 60 minutes, which is 2:00 when formatted as time, or 0.08333333 when formatted as general. 0.083333 of a day is 2 hours or =2/24. Look up how excel handles times and dates in the Help file for a much more detailed explanation. HTH Martin "ViestaWu" wrote in message ... Dear all, just now I input 1:198 in one cell of my worksheet, it shows me 0.17916(6 is cycling). I feel strange, cause I havn't input "=" befor it. and I don't know what kind of operation Excel does to me. try with 1:60, result is 0.833333333. and I found when the number in position of 198 is large than 59(from 60), it will does this strange thing. if the number is smaller than and equal to 59, the data will be the time. could anyone give me an explanation that what excel does to me? thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"cannot Empty the Clipboard" strange message | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Strange "FormatCells" behaviour | Excel Worksheet Functions |