Mar 06 2021 09:48 AM
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
Mar 06 2021 10:58 AM
What 1:90 shall practically mean, 1hour 90 minutes? Or you mean 1.9/24 = 01:54:00 ?
Mar 06 2021 11:23 AM
@Sergei Baklan yes it should show 1hour 90 minutes
Mar 06 2021 12:47 PM
With permission, everyone
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Mar 06 2021 01:18 PM
Mar 06 2021 01:44 PM
Mar 06 2021 03:17 PM
Mar 06 2021 03:44 PM - edited Mar 06 2021 03:45 PM
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)
Mar 06 2021 03:45 PM
Mar 06 2021 04:57 PM - edited Mar 06 2021 05:07 PM
@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
Mar 06 2021 05:03 PM - edited Mar 06 2021 05:04 PM
@JMB17 Thanks for the help. I need it in time value as I will be adding it up.
Mar 06 2021 07:01 PM
Mar 06 2021 11:03 PM
@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).
Mar 07 2021 08:21 AM - edited Mar 07 2021 08:22 AM
@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.
Mar 07 2021 09:03 AM
@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.
Mar 07 2021 11:45 AM
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)