Ratio keeps converting to decimal

[Herotrader]

Hello

I'm trying to insert 20:60 into a cell but the cell keeps converting the 20:60 into the number 0.875.
I dont want that. I just want the cell to say 20:60

How can I fix that please?


I've got other cells that say 20:40 , and 20:20 and they look just fine

[Shareez Saleem]

Right Click > Format Cells > Custom > Write HH:MM under Type:
FYI, 20:60 = 21:00, so the cell will show 21:00

[torachan]

format the cell as 'Text'

[FDibbins]

Right Click > Format Cells > Custom > Write HH:MM under Type:
FYI, 20:60 = 21:00, so the cell will show 21:00

I think OP is trying to show ratios, not time.

Herotrader, the reason they look OK is that they are actually being shown as time, not a ratio. In excel, time is just a fraction of a day (24 hrs), 6:00 is actually If you think of this 0.25, 18: is 0.75, so based on what you show...
20:60 is actually 21:00 (20:00 hrs plus 60 minutes) = 0.875
20:40 is 40 mins past 8 PM
20:20 is 20 mins past 8 PM

Make sense?

[Herotrader]

Right Click > Format Cells > Custom > Write HH:MM under Type:
FYI, 20:60 = 21:00, so the cell will show 21:00

Hello, I'm trying to show 20:60 ratio. Not time.
Thanks

[Herotrader]

I think OP is trying to show ratios, not time.

Herotrader, the reason they look OK is that they are actually being shown as time, not a ratio. In excel, time is just a fraction of a day (24 hrs), 6:00 is actually If you think of this 0.25, 18: is 0.75, so based on what you show...
20:60 is actually 21:00 (20:00 hrs plus 60 minutes) = 0.875
20:40 is 40 mins past 8 PM
20:20 is 20 mins past 8 PM

Make sense?

Yes I'm trying to show a RATIO. How do I do that?

[Herotrader]

Hello

I've fixed the problem in a roundabout way.

I've put letters in the same cell. I suppose this confuses Excel and it keeps the 20:60 format

I've written:

RR 20:60

[Pepe Le Mokko]

You could also precede the string with an apostrophe (')

[torachan]

You could of course formatted the cell as 'Text'.
If you then need to do 'maths' on the cell, you have a rather convenient separator ' : '
Rather than adding inbuilt problems.

[FDibbins]

you could also just add a space at the start