Excel based Timecode Calculator

[Kathmandale]

Hi,

a little while ago I built this excel sheet with functions for timecode calculations. (A lot of it was lifted from the work done by Allen Wyatt at Excel Ribbon). Basically, with macros enabled, two new functions are added to excel that convert a timecode value (HH:MM:SS:FF) to a total number of frames and then back again. This means you can build formulas to easily do maths with large numbers of timecode values.

We used this primarily to populate a lot (over a thousand) timecode events in an ETC lighting desk Show Control list (using a .csv import on the desk). We also found it useful to automate the editing of some video content we were making in Adobe CC based on in-out-duration of the shots in the same list, but that's another story.

Once you've done the calculations you need on your spreadsheet you can export as a tab-seperated document for use in Isadora with Data Array, Timecode Comparator etc etc.

Happy to put this on the Plugin Page but wasn't sure if it's appropriate. Let me know if so and I'll post it up with some instructions.

[Kathmandale]

Sorry, I tried to include the file in the above post but I think it is perhaps being blocked. It's a macro-enabled-excel document which I imagine may look potentially malicious.

[mark]

@kathmandale said:

Sorry, I tried to include the file in the above post but I think it is perhaps being blocked. It's a macro-enabled-excel document which I imagine may look potentially malicious.

 Try putting it in a .zip file.

Best Wishes,
Mark

[Kathmandale]

@mark Thanks Mark, here it is.

ITD Timecode Cheat Sheet.xlsm.zip

[paddykelly]

Andrew this is incredible. I've spent the last week trying to find a way of doing the timecode durations calculation then converting to and from total frames. This does the job wonderfully.

I'm working in a US based 23.98 frames system though. 

I changed the frame rate on the first page and saved and reopened as suggested before making any other changes. 

Testing it, when it's asked for number of frames in 1 second it returns 24 though. Even when set to 2 decimal places it's still 24.00. I'm wondering if this is an excel rounding up thing rather than it not recognising the frame rate has changed? 

I've gone back over this all a few times to make sure I didn't make a mistake. I've also done a version where I format the E5 cell on the set up page as a number format with 2 decimal places and that has no effect

Is there something fundamental in the macro that prevents decimal places in the frame rate. Obviously the variance will be very small but it's an important fraction over the duration of the film 

[Kathmandale]

@paddykelly Hi Paddy, glad you're finding it useful. It's been a while since I last looked at this. I've just opened it up and currently the macros are built around integer values so they will round each frame to a whole number. I'll dust off visual basic later this evening and see if I can re-work it to accept floating points...

(I might be back on later to ask for help)

[Kathmandale]

@paddykelly Hi again, I've been thinking about this and maybe coming up with more questions than answers. I might be wrong with I'm about to say so please, anyone who knows better jump in!

The key thing to remember is that timecode is a reference on a recording for when each complete frame was recorded. The recording still only consists of complete frames, there are no 'point' frames; timecode only works with complete frames. How fast you play these frames back (at 23.976, 23.98, 24, 29.97, 30 etc) is a different question and only really becomes relevant when you want to compare your recording with other material (say a sound recording) or a clock in the real world.

I think it comes down to what you want to do with the timecode calculations you're making. There are two scenarios as I see it:

1) You are comparing/calculating timecode from a single source, or from sources that are all recorded using the same timecode base.

2) You want to compare timecode from sources using different timecode standards OR you want to compare them to a 'real-world' clock that runs in hrs:mins:sec.dec

In the first instance you shouldn't need to change anything. Even using NTSC rates like 23.976 or 23.98 you can never view a 'point' of a frame, you still want to perform calculations based on whole frames. So, if all your material is at 23.98 and you do calculations using 24fps everything should still match up. If you want to find the reference from the frame that comes 01:00:00:12 after 00:00:01:12 then you add the two together and get 01:00:02:00. The 0.02 frames per second that are 'dropped' are irrelevant in this case as each whole frame was still recorded. When you come to play them back the fractionally different rate will drift over time relevant to a real clock, but that it doesn't change the 'whole frame' calculations you need to do.

The problems come when you try to compare it to the real world clock or to other material recorded at a different rate and the 'drift' between the timecode reference points of each frame in your recording and the real time become apparant. This  drift equates to 3.6 seconds per hour. After exactly 1 hour (on a real world clock) your timecode will read 01:00:03:14 because of the 0.2fps drift.

Now, I might be wrong, but I think if you need to compare something recorded at 23.98 to something recorded at a 'whole frame' rate, or to a real clock, then I'd perform the calculations based on 24 and then readjust everything by 3.6 seconds an hour. I guess using the tools in the excel sheet you could do this by doing something like:

=Num2Time(Time2Num(A1)/(23.976/24))

The function above returns 01:00:03:14 if 'A1' contains 01:00:00:00 based on a 24 frame rate, which should sync you up with a real clock.

Let me know how you get on!

[paddykelly]

@Kathmandale 

Yeah, that all makes complete sense. I was over thinking it and you're right, at no point does the 0.02 dropped actually make a difference. Especially with the doc I'm trying to create where frames will probably get rounded down to the nearest second anyway

Thanks for give this some thought for me though!

Do you remember if you ever hit any issues with this macro?

I'm using your worksheet as a base with the macro active I'm creating a sheet with columns for the following

(A) Video Source

(B) Timecode in

(C) Timecode out

(D) Duration

(E) Duration as frames

I'm using the macro to work out the difference between B and C =Num2Time(Time2Num(G2)-Time2Num(F2)) I'm then converting that time duration to frames in column E. 

The on a seperate tab I'm using a SUMIF to collect the values from column E based on the video source in column A, before converting those frame totals back to time. Basically it's intended to add up all of the sources across the piece of content to give me a total from each supplier. 

It all works which is amazing. 

But then if I drag the formulas down the columns or add any, suddenly it all stops working and I get the frustrating #VALUE!  

I'm looking at the formulas in each cell and they're all correct. If I rebuild it all from scratch it starts working again. But if anything is added or dragged again, it stops working. I can't for the life of me see what's changing to throw it out. 

I've not really used macros before. Is there anything fundamental I'm doing which would cause this do you think?

[Woland]

@kathmandale said:

Happy to put this on the Plugin Page but wasn't sure if it's appropriate. Let me know if so and I'll post it up with some instructions.

 Yes please! That'd be wonderful :)

[Kathmandale]

@paddykelly Hi, I don't think I've had any issues, certainly not how you've described. I've not done a lot with Macros in excel either, this was my first proper project. I did sometimes find it got a bit sluggish when working with a lot of values. The show we built this for had references for every edit in Night of The Living Dead, about 1100 shots. If we made changes I'd sometimes need to re-load a sheet or quite restart to get it to populate properly.

I have had problems with Sumif before though, you need to be careful when you drag/copy/paste that you're still referencing the right areas. So for example, if in cell C12 you have the formula =SUMIF(A1:A10,"video1",B1:B10) and you copy/drag this to cell C13 it will copy as =SUMIF(A2:A11,"video1",B2:B11). If you copy it to D12 it will give you =SUMIF(B1:B10,"video1",C1:C10)

This is because by default excel treats cell references as 'relative', so when you copy/paste/drag a function it will re-refrence the ranges in the formulas based on their relative position to the new cell. This is really handy when copying a 'total' for example all down one side of a table. It's more of an issue when your references are two dimensional.

Luckily there is an easy way around it. You can make references 'absolute' rather than 'relative' by adding the $ sign to your formulas. Placing a '$' before a column or row reference makes it absolute, so when you copy/paste/drag that value wont change. So, using the same example as before, if in cell C12 you had =SUMIF($A$1:$A$10,"video1",$B$1:$B$10) and copied it to C13 you would still have =SUMIF($A$1:$A$10,"video1",$B$1:$B$10).

You can use '$' selectively to only make a column absolute but keep the rows relative for example by using $A1:$A10.

Not sure if that will fix your issue but might be worth checking. I think Time2Num and Num2Time will return a #VALUE error if they reference any cells that aren't formatted how they expect them to be.

I'm going to be away for a few weeks now so won't be responding very quickly to things. Good luck.

[irlsanders]

This is super helpful. Could anyone advise how to sum and display  all the durations in Column D? I need to do a reality check on timecodes from cue sheets to make sure they don't exceed the duration on the show. Thanks In Advance!  :)