I need to use a specific Excel template to input timecodes (cue-IN and cue-OUT) and get the duration for each set (cue-OUT minus cue-IN) as well as the grand total of all the duration (total running time). The formula for each row to figure out the duration is given but there is no formula provided to figure out the total running time. Could you kindly take a look at the attached sample file and provide me with the formula for the total duration (orange cell)? I would appreciate it very much. The following are the formulae for two different frame rate (frame per second).
30FPS
=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86401,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),30),"\:00")
24FPS
=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86400,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),24),"\:00")
Last edited by romperstomper; 07-08-2011 at 05:30 PM. Reason: Mark solved
Try:
used with Ctrl-Shift-Enter.=SUM(VALUE(C2:C35))
Cheers,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
Thank you very much for you advise. However, it does not seem to be working.
I should have been more thorough in my original post. The values in each cell are HH:MM:SS:FF (HH=hours, MM=minutes, SS=seconds and FF= frame). In the sample file, the frame rate is 30, which means when FF is 30, it carries over to SS. You will never see 30 in FF. If the frame rate is 24 you will see only 00 through 23 and 24 will carry over to MM.
You'll see only 00 through 29. In SS (seconds) and MM (minutes) you will see only 00 through 59.
When I do the addition manually, the total duration is 00:18:13, rather than 01:11:13:00 which I get from your formula.
I hope the above information is helpful in revising the formula.
Thanks again for your time and help!
@hbsunshine, I believe these questions were resolved previously:
http://www.excelforum.com/excel-new-...-timecode.html
I am not sure why you are looking to use a different method - you should note that in your latest sample the values being returned are not presently Time values but text strings - were you to coerce you would note that Frames are being stored as Seconds.
To reiterate the formulae presented previously
where F1 holds fps value be it 24/30 etc...C2: =("0:"&TEXT(DOLLARFR(ROUND(((LEFT(B2,8)*(86400*$F$1)+RIGHT(B2,2))-(LEFT(A2,8)*(86400*$F$1)+RIGHT(A2,2))),0)/$F$1,$F$1),"0.00"))+0 format as hh:mm:ss.00 C37: =SUMPRODUCT(FLOOR($C$2:$C$35,"0:1.0"))+("0:"&TEXT(DOLLARFR(SUMPRODUCT(ROUND(MOD($C$2:$C$35*86400,1)*100,0))/$F$1,$F$1),"0.00")) format as hh:mm:ss.00
edit: also I believe the sum should be 1:18.30 rather than 18.30 as implied.
Last edited by DonkeyOte; 03-06-2011 at 02:31 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you for your time and yes, you are right. The sum should be 1:18:30. It was a typo. Thank you for catching that.
I did post the previous thread and it worked fine with the sample file. However, when I used it with far more rows in real projects, I noticed that the results were not always correct. It was inconsistent (sometimes it was correct and sometimes it wasn't). So I ended up having to verify the results manually. I was not sure if I could re-open the resolved thread. In the meantime, I was given a different template with a new formula but it does not have the total sum formula and I am having to do it manually.
I am still a very beginner and this is way over my head at this point. So, I was hoping that I could get some help.
Thank you in advance for your patience and guidance. Sincerely.
Perhaps you could post an example which demonstrates the inaccuracies you refer to above ?
I'm not saying the formula I suggested is bullet proof or optimised - I'm sure it isn't - but based on the examples it does what you requested and stores frames as ms which appeared to be a requirement (your latest template does not).
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you for your reply. I have attached the screen shot of the result being 01:11:13:00 rather than 1:18:13.
I will look for the inconsistencies from before with the previous formula.
Thank you.
Yes as mentioned your existing template is storing frames as seconds (in string form)
The result of this is that when coerced/summed the calculation uses base 60 for frames (rather than 30, 24 etc...)
Even if stored as ms you need to account for the base variance.
If you could post back with a sample illustrating errors with the prior suggestion that would be good - for the sample provided here it generates all of the expected results (individual & aggregate) - for me at least.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Here is one example in which there are 168 rows. What I did was... I inserted additional rows to the original template you prepared for me to accommodate more rows, dragged the lower right corner of the last duration cell of the original template all the way down to the new last duration cell. The duration of each row was fine, but the total duration is supposed to be over 12 minutes.
When I had over 300 rows in another project, the end result was very different from what was supposed to be.
I would appreciate it if you could take a look at it.
Also, what is the most appropriate way to ask a question when a thread is already resolved?
Thank you very much for your help.
The formula works you must however adjust the precedent ranges such that they encompass the data.
not use of C2:C169 rather than C2:C35 as was previously the caseC170: =SUMPRODUCT(FLOOR($C$2:$C$169,"0:1.0"))+("0:"&TEXT(DOLLARFR(SUMPRODUCT(ROUND(MOD($C$2:$C$169*86400,1)*100,0))/$F$1,$F$1),"0.00"))
result of the above would be 12:09.20
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
As I am reading/watching some basic tutorials, I have just noticed the critical error I made. I think I should changed the "end" cell number in the formula for in the total duration. Is this correct?
Do I change this number manually each time? Or, is there a smarter way to do this?
I apologize for my slow learning curve. Working, raising children and studying all at once seem to slow me down.
THANK YOU SO MUCH.
I appreciate your thorough explanations. It is finally making sense to me.
I truly appreciate your patience. You are very kind. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks