Kiki Burgh Posted July 18, 2008 Share Posted July 18, 2008 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.966667which is equivalent to 360 (6 x 60 in minutes) + 58 remaining secondsthe 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. Link to comment Share on other sites More sharing options...
CoffeeFiend Posted July 18, 2008 Share Posted July 18, 2008 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. Link to comment Share on other sites More sharing options...
Kiki Burgh Posted July 19, 2008 Author Share Posted July 19, 2008 (edited) 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 Link to comment Share on other sites More sharing options...
CoffeeFiend Posted July 19, 2008 Share Posted July 19, 2008 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. Link to comment Share on other sites More sharing options...
Kiki Burgh Posted July 21, 2008 Author Share Posted July 21, 2008 actually learned the 2nd way already. was thinking more along the path of what you 1st shared (which was very helpful). thanks again crahak! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now