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

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

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.

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

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

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