Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Footage to seconds (# to mm:ss).
I was wondering if anyone might be so kind as to help me with a formula. I currently have 1000s of film items entered on a spreadsheet with length indicated by number of feet. I know that each 1.66666666 feet is worth one second of running time. Is there a way to just apply a formula and have results displayed as mm:ss rounded to the nearest second? AD -- a_dunn69 ------------------------------------------------------------------------ a_dunn69's Profile: http://www.excelforum.com/member.php...o&userid=28680 View this thread: http://www.excelforum.com/showthread...hreadid=483597 |
#2
|
|||
|
|||
Footage to seconds (# to mm:ss).
One way, assume the range with films are called MyRange (replace it with
cell references like A1:A1000 or something), then you can get the mm:ss like =INT(SUM(MyRange)/1.66666666)/24/60/60 -- Regards, Peo Sjoblom "a_dunn69" wrote in message ... I was wondering if anyone might be so kind as to help me with a formula. I currently have 1000s of film items entered on a spreadsheet with length indicated by number of feet. I know that each 1.66666666 feet is worth one second of running time. Is there a way to just apply a formula and have results displayed as mm:ss rounded to the nearest second? AD -- a_dunn69 ------------------------------------------------------------------------ a_dunn69's Profile: http://www.excelforum.com/member.php...o&userid=28680 View this thread: http://www.excelforum.com/showthread...hreadid=483597 |
#3
|
|||
|
|||
Footage to seconds (# to mm:ss).
In A1, put the number of feet of film you have. In B1, put the
following formula: =A1/1.67*0.000694444444444442 custom format as [h]:mm:ss This should give you what you want. |
#4
|
|||
|
|||
Footage to seconds (# to mm:ss).
Thanks for the quick responses. -- a_dunn69 ------------------------------------------------------------------------ a_dunn69's Profile: http://www.excelforum.com/member.php...o&userid=28680 View this thread: http://www.excelforum.com/showthread...hreadid=483597 |
#5
|
|||
|
|||
Footage to seconds (# to mm:ss).
If the number of feet is in A2, then =A2/1.66666666/(24*60*60)
Divide by 1.6666 to get to seconds, then divide by 24*60*60 to get fractions of a day, since Excel equates the number 1 to a day. Then just format, number, custom h:mm:ss. "a_dunn69" wrote: I was wondering if anyone might be so kind as to help me with a formula. I currently have 1000s of film items entered on a spreadsheet with length indicated by number of feet. I know that each 1.66666666 feet is worth one second of running time. Is there a way to just apply a formula and have results displayed as mm:ss rounded to the nearest second? AD -- a_dunn69 ------------------------------------------------------------------------ a_dunn69's Profile: http://www.excelforum.com/member.php...o&userid=28680 View this thread: http://www.excelforum.com/showthread...hreadid=483597 |
#6
|
|||
|
|||
Footage to seconds (# to mm:ss).
Two steps to think about
first convert feet into seconds then convert number of seconds into a time as understood by Excel Your conversion factor of 1.66666... would be better thought of as 5/3 so feet*3/5 = seconds next the time bit. If you format a cell as date or time, Excel treats 1as a day which is 24 hours or 1440 min (24*60) or 86400 sec (24*60*60) so seconds/24/60/60 formatted as time will display correctly however because you want mm:ss custom format as [mm]:ss the [ ] allow the mm to go above 59 without showing hours If you do want hours [hh]:mm:ss In full if feet value is in A1 formula is =A1*3/5/24/60/60 Custom formated [mm]:ss hth RES |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting minutes and seconds to calculate a total average | Excel Worksheet Functions | |||
Sum minutes and seconds to total hours | Excel Discussion (Misc queries) | |||
How do I convert 00:03:54 to get 234 seconds? | Excel Worksheet Functions | |||
Convert seconds to minutes and seconds in excel | Excel Worksheet Functions | |||
convert seconds to minutes and seconds | Excel Worksheet Functions |