Sign in with
Sign up | Sign in
Your question

Converting decimals to time formula needed

Last response: in Apps General Discussion
Share
January 2, 2013 4:42:14 AM

trying to find a formula on converting decimals to time. I'm making a spreadsheet for a game >..> and i am trying to manage something.
say i have this.. 7.72=days I need a formula for google docs to convert that into the actual time.
January 2, 2013 6:59:29 PM

Here is the way to do it by hand, and you can translate it:

0.72 * 24 hours per day = 17.28 = 5PM and 0.28 of an hour
0.28 * 60 = 16.8 = 16 minutes and 0.8 of a minute
0.8 * 60 = 48 seconds

So, 0.72 of a day = 5:16:48PM

m
0
l
January 28, 2013 5:03:25 AM

well I guess I said it wrong ._.What I meant was I want the decimals to come out like this days:hours:minutes:seconds. Also, what would be interesting if I can get a way to take that time and add it to current time to get the finish time. so say its 1/30/2013 5:32:19pm right now. I get 6.34 days. take that time and get the completion time. How would I do this? I don't know very many formulas for google docs.
m
0
l
Related resources
January 28, 2013 9:21:13 PM

I already gave you all the details you need ;) 
m
0
l
October 22, 2013 5:24:28 AM

Hi, I don't know if you have solved your issue by now, but I guess not since PhilFrisbie's comments don't really help.

I just had the same issue with a time which was 3.89 in field C4
So I used: =FIND(".",C4) that returns me the position of the . which is 2
=Left(C4,D4-1) which returns me 3
=right(C4,D4) which returns me 89
Just multiply 89*0.6 and you have minutes

I tried to do it by using the function =split() but couldn't make it to work on a calculated field.
m
0
l
October 22, 2013 5:41:06 AM

Oops sorry

For =right(C4,D4) it's not correct. you need to round with
=ROUNDUP(C4,2) so you have 2 decimals
And then you do =right(C4,2) so it returns you the decimals.

Then you can even be fancy and concatenate
=CONCATENATE(F4,$G$3,H4)
in F4 i have 3
in G3 I have H
in H4 i have 54 // (83*0.6)
so it returns me 3h54

m
0
l
!