Hi I am trying to convert a number format in one cell to time format in another cell. Here is my exampleIn 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

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:30NikolinoI know I don't know anything (Socrates)

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.

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.

sliao8788 For what it is worth, here's a numerical variant. That is, without text manipulation.=INT(A1)/24+MOD(A1,1)*100/1440formatted as time "h:mm". As in JMB17 's solution, it will return 2:30 (2 hrs and 30 minutes).

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.

convert numbers to time | Microsoft Community Hub

16 Replies

NikolinoDE
Gold Contributor
Mar 07, 2021
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)
AAAAPunkt_Doppelpunkt.xlsx9 KB
NikolinoDE
Gold Contributor
Mar 06, 2021
sliao8788
With permission, everyone

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)
- sliao8788
  Copper Contributor
  Mar 06, 2021
  If I do this it changes the value to 21:36 and not leave it as 1:90
  - NikolinoDE
    Gold Contributor
    Mar 06, 2021
    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)
SergeiBaklan
Diamond Contributor
Mar 06, 2021
sliao8788

What 1:90 shall practically mean, 1hour 90 minutes? Or you mean 1.9/24 = 01:54:00 ?
- sliao8788
  Copper Contributor
  Mar 06, 2021
  SergeiBaklan yes it should show 1hour 90 minutes

Forum Discussion

convert numbers to time

16 Replies

Resources