• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

suming up time codes with frames

M

melaniez

New Member
Hello


I know this subject has been addressed many times in forums but I'm hoping I could get an answer specific to my case.


I have a range of time codes in this format: ss:ff (seconds and frames) that I would like to be able to sum up and get an accurate result, with the obvious issue of frame range not being supported by excel.


Because the spreadsheet would be used by a number of people, i would like to avoid macros and use a formula solution.

I have come across this formula and I'm wondering how to adapt it for my specific time code format:


=TEXT(LEFT(E11,8)-LEFT(D11,8)-(RIGHT(D11,2)>RIGHT(E11,2))/86400,"s")&TEXT(MOD(RIGHT(E11,2)-RIGHT(D11,2),24),":00")


The range would be spread across a row (not in a column) with one time code per cell. The result would appear in the cell furthest right of the range.


Also, is there a way to link this formula to a different cell where the frame range would be input (therefore allowing to change the frame rate without having to modify the formula).


thank you in advance for your help!
 
Hi, melaniez!

Can you please upload a sample file? I can't reproduce an output according to your definition on input data (D11 and E11). Thanks.

Regards!
 
Hi SirJB7


Thanks for your reply. I'd be happy to upload a sample, but I'm afraid I don't know how :p Is there a link on this page that allows you to do so?

As far as the formula I copied, this is from another forum thread, I haven't actually be using it so the D11 and E11 would not be relevant in my case anyway...I just thought there might be a way to modify that formula so that it applies in my case...
 
Hi ,


I am not sure what your requirement is ; what do frames mean ?


Can you just post about 5 data items , and specify what you want the result to be ?


Narayan
 
Thanks oldchippy!

I posted an example here:

https://docs.google.com/spreadsheet/ccc?key=0Aiw0RFW1ZgTndGI2WVVLb1A2NnJQVGc1VGNKNk44M3c


the blue columns are data manually input. The format is ss:ff were s are seconds and f are frames. The frame range is 24/s so what I need is to be able to sum these number up taking that into account.

In the first row I added the sum in the grey column and in the next column, what the result should actually be.

0.57 frames is actually 2 seconds and 9 seconds (2x24 frames + 9 frames)so the result should be 20.09 and not 18.57.


The formula I posted above apparently makes it possible to subtract time codes while taking the frame rate in account so I am wondering if there is a way to adapt it to allow me to add up several time codes (i'm not including minutes or hours in my time code). From what I understand, it breaks down the time code into parts and treats each separately.

Here's where I found it, if that helps:

http://www.excelforum.com/excel-new-users/764752-formula-for-subtracting-and-adding-timecode.html
 
Hi, melaniez!

If you have the data in columns A thru D, try this:

E1: =SUM(INT(A1)+INT(B1)+INT(C1)+INT(D1))

F1: =(A1-INT(A1)+B1-INT(B1)+C1-INT(C1)+D1-INT(D1))/0.24

G1: =(F1-INT(F1))*0.24

H1: =E1+INT(F1)+G1

In H1 your result, using three auxiliary columns. If you can't use them, just combine the three formulas in one as this:

E1: =SUM(INT(A1)+INT(B1)+INT(C1)+INT(D1))+INT((A1-INT(A1)+B1-INT(B1)+C1-INT(C1)+D1-INT(D1))/0.24)+((A1-INT(A1)+B1-INT(B1)+C1-INT(C1)+D1-INT(D1))/0.24-INT((A1-INT(A1)+B1-INT(B1)+C1-INT(C1)+D1-INT(D1))/0.24))*0.24)

Regards!
 
Thank you SirJB7, I've used the whole formula in one cell and it seems to be working like a charm! Brilliant!
 
Ok, now here's another question.

If I enter text into the cells instead of time codes, is there a way to exclude these cells from the calculation, so as not to get a #value error. I would know how to use an IF formula on a range but since we're looking at each cell individually here, I'm not sure how... (see me updated example, row 2)


https://docs.google.com/spreadsheet/ccc?key=0Aiw0RFW1ZgTndGI2WVVLb1A2NnJQVGc1VGNKNk44M3c
 
ok, I've got it. I modified your formula slightly to treat the cell as a range rather than individually, then I added a sumif formula to each segment to exclude any cell that contained text and not numbers:


=(INT(SUMIF(A2:D2,">0",A2:D2)))

+(INT(((SUMIF(A2:D2,">0",A2:D2))-INT(SUMIF(A2:D2,">0",A2:D2)))/0.24))+(((SUMIF(A2:D2,">0",A2:D2)-INT(SUMIF(A2:D2,">0",A2:D2)))/0.24-INT((SUMIF(A2:D2,">0",A2:D2)-INT(SUMIF(A2:D2,">0",A2:D2)))/0.24))*0.24)


thanks again for your help, I could not have made it this far without your input...
 
You must log in or register to reply here.
Back
Top