# Convert time range into minutes formula

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.

-
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

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

-
(+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