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

Thanks

16 Replies

# Re: convert numbers to time

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

# Re: convert numbers to time

@Sergei Baklan yes it should show 1hour 90 minutes

# Betreff: convert numbers to time

With permission, everyone Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

# Betreff: convert numbers to time

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

# Betreff: convert numbers to time

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)

# Betreff: convert numbers to time

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.

# Betreff: convert numbers to time

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)

# Betreff: convert numbers to time

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.

# Betreff: convert numbers to time

@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

# Betreff: convert numbers to time

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

# Betreff: convert numbers to time

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.

# Betreff: convert numbers to time

@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). # Betreff: convert numbers to time

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

# Betreff: convert numbers to time

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

# Betreff: convert numbers to time

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

# Betreff: convert numbers to time

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)