I'm stumped with this one so hoping someone can help please.
I've been asked to create a sheet that contains a formula to convert decimal time into hours and minutes to three places.
e.g. 1.53 would become 1.318 (because .53 of an hour is 31.8 minutes).
The closest I have come is to put the 1.53 in cell A1, then:
B1 =LEFT(A1,(FIND(".",A1,1)-1))
C1 =(MID(A1,FIND(".",A1)+1,256))
D1 =6*C1
E1 =CONCATENATE(B1,".",D1)
This returns 1.318 but the method doesn't work if cell A1 is a whole number, say 1.00 or 2.00.
Also, 1.1 comes out as 1.6 (an hour and 6 minutes as it should) but so does 1.01 (which should be 1.06).
Any suggestions gratefully received.
I've been asked to create a sheet that contains a formula to convert decimal time into hours and minutes to three places.
e.g. 1.53 would become 1.318 (because .53 of an hour is 31.8 minutes).
The closest I have come is to put the 1.53 in cell A1, then:
B1 =LEFT(A1,(FIND(".",A1,1)-1))
C1 =(MID(A1,FIND(".",A1)+1,256))
D1 =6*C1
E1 =CONCATENATE(B1,".",D1)
This returns 1.318 but the method doesn't work if cell A1 is a whole number, say 1.00 or 2.00.
Also, 1.1 comes out as 1.6 (an hour and 6 minutes as it should) but so does 1.01 (which should be 1.06).
Any suggestions gratefully received.