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