convert numbers to time

[sliao8788]

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 formula do I use.

Thanks

[#2]

@sliao8788 

What 1:90 shall practically mean, 1hour 90 minutes? Or you mean 1.9/24 = 01:54:00 ?

[#3]

@Sergei Baklan yes it should show 1hour 90 minutes

[#4]

@sliao8788 

With permission, everyone

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#5]

If I do this it changes the value to 21:36 and not leave it as 1:90

[#6]

You are absolutely right, was too hasty ... I have to give me more time to let everything through.
File> Options> Document Review>
For this purpose you could create an entry in the autocorrection:
Replace:, (comma)
through:: (colon)
It is now possible to enter a time in the following format: 10,, 20,, 30 and this automatically becomes 10:20:30


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

[#7]

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.

[#8]

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)

[#9]

If you just want it to display "1:90", then you could try:
=SUBSTITUTE(TEXT(A1,"0.00"),".",":")

But, it will be a text value, so you won't be able to perform any arithmetic operations with it very easily.

I believe you could use:
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"0.00"),".",":"))

and format it as [h]:mm and it would be an actual numeric time value, but it will display as "2:30" and not "1:90". If you want it to display as hours:minutes, then I'm not sure how you stop excel from incrementing the hours when you go past 60.

[#10]

@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

[#11]

@JMB17 Thanks for the help. I need it in time value as I will be adding it up.

[#12]

Then, I believe the second option will work.
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"0.00"),".",":"))

Which is the same as what Nikolino is suggesting, but formatting the number to 2 decimals before replacing the "." with ":" as it appears excel interprets "1:9" as 1 hr 9 minutes. So, it seems you have to force the two decimals to get it to see it as 90 minutes.

Numerically, 2:30 is the same as 1:90, so it should make no difference for your calculations.

[#13]

@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).

[#14]

@JMB17, Thanks for the response. This seems to be working. Appreciate your help. Now I have come up with another problem. Eg: Cell A1, if I have a number that is 26.00 with the formula in cell B1, it returns with 2:00 and not 26:00. Is there a way to make excel recognise this number as I know the time in excel is 24 hrs period. Also thanks to @NikolinoDE and @Riny_van_Eekelen for you help.

[#15]

Try changing the number format to: [h]:mm

[#16]

@JMB17 Thanks for the response.  When I tried it with your formula, it didn't work even with changing the format to [H]:mm, but when I tried it with the formula @Riny_van_Eekelen  suggested, it worked.  

Sincere thanks to @JMB17 @Riny_van_Eekelen  and @NikolinoDE  for all your help.  Appreciate it.  

[#17]

@sliao8788 

Here again a file with the example from 1.9 to 1:90.

One would only have to format 1.9 as text.

But if you want it to remain time and to be seen as time then here is a link and a formula.

Insert colon between numbers to make them as time format with formulas

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)