Forum Discussion
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...
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.
=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.
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.
- Try changing the number format to: [h]:mm
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.