Forum Discussion

Copper Contributor

Mar 06, 2021

convert numbers to time

Hi I am trying to convert a number format in one cell to time format in another cell. Here is my example In cell A1 I have a number 1.90 and I need it to be converted into 1:90 in cell B1. what for...

Formulas and Functions

sliao8788

Copper Contributor

Mar 06, 2021

Thanks, This kind of solves the problem. but this was what I was trying to do,
In cell A1 I have a number 1.90 and I need it to be converted into 1:90 in cell B1. what formula do I use.

NikolinoDE

Gold Contributor

Mar 06, 2021

Formula from excel 2010
=SUBSTITUTE(A1,".",",")*1

This formula was available from Excel 97
=REPLACE(A1,FIND(".",A1),1,":")*1

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)

sliao8788
Copper Contributor
Mar 07, 2021
NikolinoDE Thanks for all the help. Tried this method and it is giving a value of 1:09 and not 1:90. if I change the number to 1.91 in cell A1, it gives me a result of 2:31
- Riny_van_Eekelen
  Platinum Contributor
  Mar 07, 2021
  sliao8788 For what it is worth, here's a numerical variant. That is, without text manipulation.
  =INT(A1)/24+MOD(A1,1)*100/1440
  formatted as time "h:mm". As in JMB17 's solution, it will return 2:30 (2 hrs and 30 minutes).