Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite

I need a formula that will convert time into minutes and round up 30 sec to 1 minute.

Example: change 7:58:31 (hr:min:sec) to 479 minutes or 08:31:29 to 511 minutes. The first example adds 1 minute to total because 31 seconds is over half a minute.

share|improve this question
Is mysql query an acceptable solution ? – wojciechz Aug 27 '12 at 16:08
1  
We'll need more information, as right now this isn't much of a question. What kind of formula are you looking for? How/where are you reading the time from? Are you looking for an answer in a scripting language? For Excel? What have you tried already? Where are you getting stuck? – techie007 Aug 27 '12 at 16:12
reading from a cell that contains imported data that is in the format of hh:mm:ss ex: F14 contains [01:02:45] and I would like to convert it to mm or F70 [63] which is the conversion to minutes in this example or 60*1+3 because 45 sec is close to 1 minute – Tom Aug 27 '12 at 16:19
reading from a cell indicates a spreadsheet, but we're still stuck with not knowing if this is Excel 97, excel 2010, lotus 1-2-3, borland quattro, google docs, or some other spreadsheet. I could be completely wrong, and you're trying to talk to prisoners, and prison population should be the tag :) – Sean Cheshire Aug 27 '12 at 16:23
something like this =((HOUR(G11)*60)+MINUTE(G11)+(SECOND(G11)/60)) – Tom Aug 27 '12 at 16:46
show 1 more comment

1 Answer

up vote 3 down vote accepted

14401 is the magic number in this case

using ROUND to round up/down will give you the resolution. With your time in A1, =ROUND(A1*1440,0) will get you the number of minutes

with your calculation of =((HOUR(G11)*60)+MINUTE(G11)+(SECOND(G11)/60)), changing to =((HOUR(G11)*60)+MINUTE(G11)+IF(SECOND(G11)<30,0,1)) would remove the decimal

1 1440 is simply 24 hours * 60 minutes

share|improve this answer
(+1) You don't really need the Round. Just format the cell as a number with 0 decimal places and it should round on its own. – David Vandenbos Aug 27 '12 at 17:17
anyone know how I would use this calculation in a way that would not have a decimal - automatically round to highest without decimal =((HOUR(G11)*60)+MINUTE(G11)+(SECOND(G11)/60)) – Tom Aug 27 '12 at 18:34
updated answer! – Sean Cheshire Aug 27 '12 at 18:39
Thank you Sean, that works great. can you modify to copy to another sheet? say I calculate the formula from imported sheet1 to Thursday / same book – Tom Aug 27 '12 at 19:08
References to another sheet are done using ! - e.g G1 on sheet1 will be sheet1!G1. If the sheet name has spaces then surround it with quotes. G1 on My Sheet would be 'My Sheet'!G1 – Sean Cheshire Aug 27 '12 at 20:06
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.