strange about ":"

[ViestaWu]

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

 

[Roger Govier]

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.

 

[MartinW]

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