convert decimal to time

[Kiki Burgh]

hi! i am not sure if this is the proper thread for this nor if this has been asked before (tried a simple search for convert, time, date ... but nothing of this sort came up so far) ... my apologies if both conditions above are true

anyways, kindly help on how i can come up with simple solution for converting decimal to time in a specific format. allow me to illustrate ...

sample:

418 seconds to be converted to minutes should give me (if using straight division): 418/60 = 6.966667

which is equivalent to 360 (6 x 60 in minutes) + 58 remaining seconds

the question now is how do i get the exact equivalent of: .96667 (from 6.96667) for the end result to reflect as: 6:48 (or even as 6.48)rather than 6.97 ... with the end result still usable for computation (when using values contained in other cells)?

what will be the formula for this? and if it involves hours already? or even if days already?

i hope my question is not too confusing ... any help will be greatly appreciated.

[CoffeeFiend]

One out of several ways to do it. Assuming 418 is in cell A1:

=INT(A1/60)&":"&INT(MOD(A1,60))

i.e. you use mod (modulo) to get the remainder of a division by 60 to get the seconds.

[Kiki Burgh]

thanks a lot crahak! i really appreciate this.

would it still be possible though to perform other operations to the end result/s?

i.e., trying =INT(A1/60)&":"&INT(MOD(A1,60)) ... assuming that 390 value is in A1 gives out in B1 (for example) the value 6:30

now if i wish to do simple arithmetic using the end result (i.e, multiply B1 by 2), it gives out: 0.541666667 instead of 13:00 ...

thanks again.

Edited July 19, 2008 by Kiki Burgh

[CoffeeFiend]

now if i wish to do simple arithmetic using the end result (i.e, multiply B1 by 2), it gives out: 0.541666667 instead of 13:00 ...

Yeah, it thinks it's 13h instead of 13 mins. No big deal, plenty of other ways...

Instead of the other formula, in my previous post, enter =A1/86400 (it'll say 0.004513889 -- that's perfectly normal; 86400 seconds in a day, and that's 0.004513889 of that). Now set the format to time, and it'll show minutes:seconds or such. It'll works fine when you do math with it.

[Kiki Burgh]

actually learned the 2nd way already. was thinking more along the path of what you 1st shared (which was very helpful). thanks again crahak!