Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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? – Ƭᴇcʜιᴇ007 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 :) – SeanC Aug 27 '12 at 16:23
something like this =((HOUR(G11)*60)+MINUTE(G11)+(SECOND(G11)/60)) – Tom Aug 27 '12 at 16:46
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. – dav 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! – SeanC 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 – SeanC Aug 27 '12 at 20:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .